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.
maximum of date
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
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.
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.
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom