Max Value in date column
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 42
- Joined: Tue Jan 18, 2005 5:48 am
Max Value in date column
Hii all,
I have a column which has date value [SAMPLE(1900-01-01)] in a character (10) field.I have to find the max date out of this column.Max function is not working. Can you help me on this
thanks
Jayant
I have a column which has date value [SAMPLE(1900-01-01)] in a character (10) field.I have to find the max date out of this column.Max function is not working. Can you help me on this
thanks
Jayant
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Charter Member
- Posts: 36
- Joined: Thu Jun 10, 2004 11:22 pm
hi jayantthakore,
i agree with Shails. In DataStage to find max value in a column, you need to use Aggregator Stage. Output of an aggregator stage is a decimal value.
To find max value of column SAMPL(1900-01-01) You need to conver it to date first and then convert it to julian date then use this julian data as your column for calculation.
With string dta type you will not get output properly.
i agree with Shails. In DataStage to find max value in a column, you need to use Aggregator Stage. Output of an aggregator stage is a decimal value.
To find max value of column SAMPL(1900-01-01) You need to conver it to date first and then convert it to julian date then use this julian data as your column for calculation.
With string dta type you will not get output properly.
Happy DataStaging
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Iconv and Oconv are not natively available in parallel jobs.htrisakti3 wrote:Have you tried:
iDate = IConv(fieldName, "D-YMD")
iDate is now integer & you can easily Max(iDate),
But you have to: OConv(iDate, "D-YMD")
to display this back as proper date
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
hi i got a different solution for this problem.
using modify stage i converted
date to----------> days_since_from_date(date), which is of date format-----to----->int32.----------->then in Aggregator..max(date in int32 format)------------>in modify stage...int32 to date...date_from_days_since(date in int32 format).
it works.
any comments?
using modify stage i converted
date to----------> days_since_from_date(date), which is of date format-----to----->int32.----------->then in Aggregator..max(date in int32 format)------------>in modify stage...int32 to date...date_from_days_since(date in int32 format).
it works.
any comments?
I think the date string format you have is almost suitable for direct selection:
[SAMPLE(1900-01-01)]
If you simply remove the dashes from the string, and convert the result to a number, then max() works.
ie, '1900-01-01' ==> 19000101
You can lexically remove the dashes, or do substrings and rembine, or whatever you wish to convert. It's got to be computationally easier than multiple up/down date conversions.
Carter
[SAMPLE(1900-01-01)]
If you simply remove the dashes from the string, and convert the result to a number, then max() works.
ie, '1900-01-01' ==> 19000101
You can lexically remove the dashes, or do substrings and rembine, or whatever you wish to convert. It's got to be computationally easier than multiple up/down date conversions.
Carter
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Why can't you just get the max of the string[10] field? You can do it in SQL, so you should be able to do it in an Aggregator stage. Dates in ISO 8601 format - which this is - sort properly, at least in the year range 1AD through 9999AD.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.