Date Conversion

[jaysheel]

Hi,

I am trying to convert a date in Varchar (mm-dd-yy) to Varchar(mm-dd-yyyy). Can anybody help me on this ?
I am a fresher in Datastage.

Thanks in advance.

[ArndW]

This can be done either with string functions or by converting to date and then back to display. In your case I would do a transform derivation of "In.ShortDate[1,6]:if In.ShortDate[7,2]<'50' then '20' ELSE '19':In.ShortDate[7,2]". This assumes that all dates are from 1951 through 2050.

[vkhandel]

you can use the following derivation in transformer......... considering the input column name as tempdate.......

tempdate[1,6]:'19':tempdate[7,2]

but the question here is how to decide about the century?? i have hardcoded that here as 19 but it can be 20 also......

[jaysheel]

Yes. The main task is understanding the century. This was earlier achieved by using user defined query in source stage. But the requirement is to achieve this using transformer stage.

[ArndW]

I think converting to the internal date first is better. To a StringToDate() call and then later a DateToString() with appropriate formatting.

[jaysheel]

Hi,

I tried the following function. But its showing error. I guess the syntax must be wrong. Can anyone correct me in this.

Store_Open_Date = StringToDate(LNK_XFRM_REAL_EST_STORES_DM.store_open_date,[%"%mm-%dd-%yyyy"%])

[ray.wurlod]

The square brackets are not part of the syntax; they indicate that the date format string is optional. Also the first "%" character in your example does not connect to anything and should therefore be removed.
The date format string describes the string being converted to date. Your example date format string does not describe the format you mentioned as the source string in your original post.

[priyadarshikunal]

I think converting to the internal date first is better. To a StringToDate() call and then later a DateToString() with appropriate formatting.

I think DataStage too uses a century pivot to decide the century
by default its 30, if its less than 30 it considers it as 20YY
and if its >30 then its converted to 19YY, as Ray mentioned in his example he used 50 as a century pivot.

Please correct me if I am wrong.

Regards,

[ArndW]

You can specify your year cutoff in either the date format (i.e. "%NNNNyy", page 31 of the Parallel Job book) or by specifying a new value for the $APT_DATE_CENTURY_BREAK_YEAR