Converting string to Timestamp

shiva459 · Post by **shiva459** » Thu Jan 17, 2008 11:49 pm

Hi All,

In my project we use a DB2 DB as source.and I am loading data to dataset.I have a column in one of the source table which is Varchar(50).The data looks like this:

11/01/2000 2:21:03 PM
10/26/2000 9:45:28 AM
12/27/2000 1:15:06 PM
10/26/2000 11:20:43 AM
10/26/2000 11:28:08 AM
11/01/2000 11:39:35 AM
10/31/2000 12:49:31 PM
11/01/2000 11:37:20 AM

I have to convert this to Timestamp before loading to the dataset.I tried these options:
1.While pulling this column from source I used TIMESTAP_FORMAT funtion in DB2.But this needs data in 'YYYY-MM-DD HH24:MI:SS format.So this option did not work.
2.I used StringToTimestamp functoin in Datastage.But this results *s in my dataset.
3.I tried using modify stage and output was NULL.

If anyone has come across same kind of problem please let me know.
Thanks in advance!!!!!!!!!

Regards,
Shiva

Maveric · Post by **Maveric** » Fri Jan 18, 2008 12:08 am

Doubt there is any AM/PM conversion by default. You can try doing it in Transformer stage.
1) Get the hours using substring function.
2) If it is AM then pass the string directly.
3) If it is PM then add 12 to the hours, concatenate it with the rest of the string and then convert it to TimeStamp.

shiva459 · Post by **shiva459** » Fri Jan 18, 2008 3:08 am

Maveric wrote:Doubt there is any AM/PM conversion by default. You can try doing it in Transformer stage.
1) Get the hours using substring function.
2) If it is AM then pass the string directly.
3) If it is PM then add 12 to the hours, concatenate it with the rest of the string and then convert it to TimeStamp.

As hour part can be single digit or double digits from my source so I am not sure how do I get this