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
Incorrect date timestamp
Moderators: chulett, rschirm, roy
Incorrect date timestamp
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.
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.
Mandy
-
- Premium Member
- Posts: 263
- Joined: Fri Sep 23, 2005 6:49 am
Re: Incorrect date timestamp
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 263
- Joined: Fri Sep 23, 2005 6:49 am
Incorrect datetimestamp
I am on datastage version 8.1, OS AIX 5.3
Thanks..
Thanks..
-
- Premium Member
- Posts: 263
- Joined: Fri Sep 23, 2005 6:49 am
incorrect datetimestamp
My input data from the source is supplied with year in theray.wurlod wrote:You've supplied a two digit year but your date format string specifies a four digit year. ...
format MM-DD-YY
DATE: 07-16-11
TIME: 01:29
The desired output a timestamp with year as YYYY
Thanks..