DSXchange

I have a requirement where I have 2 columns date field and time field and I have to output Timestamp field. How to achieve this?
DATE:11-08-11 (DATE: 06-25-11 i.e mm-dd-yy)
TIME:11:32
My thought was to concatenate them and later convert from string to timestamp, so I tried this,
StringToTimestamp((col1.DATE:' ':col1.TIME),'%mm-%dd-%yy %hh:%nn')
However I am getting this output: 1911-11-08 11:32:00

Is this is the correct approach. can somoene please let me know what I am doing wrong. I am not sure either why I am getting 1911 as the year. The correct year should be 2011

Thank
Mark

Hi Pavan,

Use StringToTimestamp((col1.DATE:' ':col1.TIME),'%mm-%dd-%1950yy %hh:%nn')

Here 1950 is year_cutoff. It means any year between 50 - 99 will be prefixed with 19 and any year between 00-49 will be prefixed with 20

Hope this might solve your problem.

Input data:

DATE: 07-16-11
TIME: 01:29

After this code change,

StringToTimestamp((Col1.DATE:' ':Col1.TIME),'%mm-%dd-%1950yy %hh:%nn')

Ouput data:

2011-07-16 01:29:00

I am seeing the desired output, However I am finding lot of warning messages in the datastage log such as this,

(1) Can someone please let me know what do I need to fix these warnings and

APT_CombinedOperatorController,0: Data string '07-16-11' does not match format '%yyyy-%mm-%dd': the value for tag %yyyy has fewer characters than expected.

APT_CombinedOperatorController,0: Conversion error calling conversion routine date_from_string data may have been lost

APT_CombinedOperatorController,0: Data string '**********01:290' does not match format '%yyyy-%mm-%dd %hh:%nn:%ss': an integer was expected to match tag %yyyy.

(2) 1950 is year_cutoff. It means any year between 50 - 99 will be prefixed with 19 and any year between 00-49 will be prefixed with 20. Is this something new in datastage? I never heard about this in the past..

Thanks in advance

You've supplied a two digit year but your date format string specifies a four digit year.

Hi Pavan,

Let me know the version that you are using?
I am not seeing any warnings in IS 8.5.

year_cutoff is always there.

I am on datastage version 8.1, OS AIX 5.3

Thanks..

ray.wurlod wrote:You've supplied a two digit year but your date format string specifies a four digit year. ...

My input data from the source is supplied with year in the
format MM-DD-YY
DATE: 07-16-11
TIME: 01:29

The desired output a timestamp with year as YYYY

Thanks..

First of all, find a way to make the year to 4 digits in your source, then use the conversion function.

Something like: MM-DD-'20':yy , So that year will become 4 digits.
But make sure, it should work for all years.