Decimal Date

[dsa]

Hi,

Input has dates in yyyymmdd or yyyymdd format like :

20100430
2009718
20101009
20090831

IF these dates have been read as decimal, how can we find maximum and minimum dates from this as well as reject the invalid dates out??

[ray.wurlod]

Maximum and minimum functions in the Aggregator stage work happily with decimal data types, or you can use stage variables in a Transformer. In a Transformer you can use IsValid() functions to test whether the string (an implicit conversion) is valid as a date.

[dsa]

Maximum and minimum functions in the Aggregator stage work happily with decimal data types, or you can use stage variables in a Transformer. In a Transformer you can use IsValid() functions to test w ...

No Ray,

dates has been read in as decimeal. So take example: 2009815, 20090109
now ideally second date is smaller than first one btu since it has been read as decimal, so simple comparison would give that first one is smaller.

and Isvalid()- how it would work when data hasn't been read as date?

[ArndW]

Since the month can be m or mm you can't do a direct numeric or string comparison. I'd convert the number to a string using the following logice:

1. stage variable svTempStringDate = In.DecimalDate
2. stage variable svStringDate = IF LEN(svTempStringDate)=7 THEN svTempStringDate[1,4]:'0':svTempStringDate[5,3] ELSE svTempStringDate

Then you can do a direct string comparison on svStringDate or convert it to a date type.

[dsa]

can we do a string comparison in data stage

as in suppose stagevar1 = "abc" and stagevar2 = "fgh"

so if I do

stagevar1>stagevar2

what would be the answer I would get?

[ArndW]

Although you can test it for yourself in a couple of minutes, the answer is that you can do string comparisons, e.g. "2010-01-23" < "2010-01-26". In your case the format is YYYYMMDD so a greater/less than comparison will work.

[dsa]

Although you can test it for yourself in a couple of minutes, the answer is that you can do string comparisons, e.g. "2010-01-23" < "2010-01-26". In your case the format is YYYYMMDD so a greater/less than comparison will work.

But for that variable is to be defined as date right?

what if variable is defined as char?

Sorry my installation has got some problem and our admin is trying to fix it so I can't test it now

[ArndW]

You are confusing me. At first it was a decimal value, then string and now you are asking about date data types. Comparing date type against each other will correctly handle < and > values regardless of the display format.

[dsa]

no no

it was date which had been read as decimal only. now i asked character because you talked about string comparison. Date came from the example you gave that string can be compared but the example you gave was of a date so it had to be read in as date right?

[ArndW]

Date and Timestamp columns have no format. Only when converting or displaying these data types is a format needed.

Internally these data types are stored so that comparisons on greater or lesser or equality are easily done.

What is the data type of your input columns?

[dsa]

input is decimal

i ma getting very much confused now

how would it know in the example i gave in my first question in this thread that it has to take these values as dates to compare them to get maximum or minimum value