Max Value in date column

jayantthakore · Post by **jayantthakore** » Tue Sep 27, 2005 5:26 am

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

shails · Post by **shails** » Tue Sep 27, 2005 5:57 am

Max function does not work on a date column. You can convert the char column to date and then use Juliandayfromdate function which gives a integer value of the date. Aply max function on this integer column. If you want date again you can use datefromjulianday function.

ray.wurlod · Post by **ray.wurlod** » Tue Sep 27, 2005 4:36 pm

What stage type are you using to apply Max() ? Given that the field is string[10], you should be able to use an Aggregator stage.

htrisakti3 · Post by **htrisakti3** » Tue Sep 27, 2005 11:43 pm

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

cmmurari · Post by **cmmurari** » Wed Sep 28, 2005 12:24 am

you should be able to use an Aggregator stage.

htrisakti3 , The basic rule is not all Basic functions work in parallel jobs

dsxdev · Post by **dsxdev** » Wed Sep 28, 2005 3:22 am

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.

kumar_s · Post by **kumar_s** » Wed Sep 28, 2005 9:38 am

hi,
Other way is to sort the data based on the group, and retain last/first using remove duplicate to get the max of date.

regard
kumar

ray.wurlod · Post by **ray.wurlod** » Wed Sep 28, 2005 4:30 pm

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

Iconv and Oconv are not natively available in parallel jobs.

dxp · Post by **dxp** » Mon Oct 03, 2005 12:30 am

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?

clshore · Post by **clshore** » Tue Oct 04, 2005 5:16 pm

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

ray.wurlod · Post by **ray.wurlod** » Tue Oct 04, 2005 11:22 pm

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.