maximum of date

DSkkk · Post by **DSkkk** » Wed Jan 19, 2005 9:50 am

hi all,
i need to extract the latest date from a column which has datatype as date. this column is extracted from teradata which is in YYYYMMDD. but when i use an aggregator MAX function, then i get an error saying that unable to convert string so taken Null. into which format should the date be converted so that i can extract the maximum value? can anyone suggest?
Thanks in advance.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Jan 19, 2005 9:57 am

DataStage's 'date' datatype is an integer value identical to Julian date starting from 31st Dec 1969.

You may hence have to convert the date using IConv function. It will be
IConv(YourDate[1, 4] : '-' : IConv(YourDate[5, 2] : '-' : IConv(YourDate[7,2] , 'D-YMD[4,2,2]')

But if the input is directly connected to an aggregator stage, maybe you can try including the MAX into your SQL statement itself.

DSkkk · Post by **DSkkk** » Wed Jan 19, 2005 11:00 am

thanks for the immediate response.
my input is not directly connected to the aggregator. so i think i need to use the iconv function. but the ouptut which i get should come only after
the oconv. right!

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Jan 19, 2005 11:07 am

Yes. If you need your value back to any other format, you need to use OConv. Format to be used will depend on how you want to use the resulting value.

DSkkk · Post by **DSkkk** » Wed Jan 19, 2005 2:23 pm

i actually got the maximum of date having YYYYMMDD format. but when i had YYYY-MM-DD it was not able to pick up the maximum. so i used a routine to strip of the extra characters in between and then used the aggregator.
anyway thanks for your attention and help.