DSXchange

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

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.

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.

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

you should be able to use an Aggregator stage.

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

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.

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

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.

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?

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

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.