String to Timestamp Conversion
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 5
- Joined: Mon Feb 07, 2011 1:17 am
- Location: madhu
String to Timestamp Conversion
How to get below string to timestamp
Input :
05-SEP-13 12.00.00.000000000 AM
01-JAN-99 11.59.59.000000000 PM
Output :
2013-09-05 12:00:00
1999-01-01 23:59:59
Thanks in advance
Input :
05-SEP-13 12.00.00.000000000 AM
01-JAN-99 11.59.59.000000000 PM
Output :
2013-09-05 12:00:00
1999-01-01 23:59:59
Thanks in advance
Sorry, Madhusan.
Would you like to have your string converted to a timestamp (which is represented internally by some integer-format in most databases) or do you need a different string-output-format for your data?
To save your data as a timestamp have a look at the transformer function StringToTimestamp(). I am not sure if the function supports the precision of your timestamp, but it just might. I haven't got a DataStage installation at hand currently, so I can't try myself.
You can then use the transformer function TimestampToString() to manipulate the output generated from a Timestamp.
Even if you just want to transform the string-representation of the timestamp it may be best to actually transform the string to a timestamp first. This will ensure the input-strings are in correct format.
Would you like to have your string converted to a timestamp (which is represented internally by some integer-format in most databases) or do you need a different string-output-format for your data?
To save your data as a timestamp have a look at the transformer function StringToTimestamp(). I am not sure if the function supports the precision of your timestamp, but it just might. I haven't got a DataStage installation at hand currently, so I can't try myself.
You can then use the transformer function TimestampToString() to manipulate the output generated from a Timestamp.
Even if you just want to transform the string-representation of the timestamp it may be best to actually transform the string to a timestamp first. This will ensure the input-strings are in correct format.
"It is not the lucky ones are grateful.
There are the grateful those are happy." Francis Bacon
There are the grateful those are happy." Francis Bacon
-
- Participant
- Posts: 62
- Joined: Thu Feb 08, 2007 6:01 am
- Location: Pune
-
- Participant
- Posts: 62
- Joined: Thu Feb 08, 2007 6:01 am
- Location: Pune
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
-
- Participant
- Posts: 5
- Joined: Mon Feb 07, 2011 1:17 am
- Location: madhu
I achieved it through trimming input time stamp micro seconds.
If year is <50 then added 20 else 19 to the year.
Note : DSLink2.A is input value
If year is <50 then added 20 else 19 to the year.
Note : DSLink2.A is input value
derivation:stgvar = Left(DSLink2.A,6):'-':(If Right(Left(DSLink2.A,9),2) >=50 Then 19:Right(Left(DSLink2.A,9),2) Else 20:Right(Left(DSLink2.A,9),2)):' ':Right(Left(DSLink2.A,18),8): ' ':Right(DSLink2.A,2)
Code: Select all
StringToTimestamp(StageVar,"%dd-%mmm-%yyyy %(H,s).%nn.%ss %(aa,w)")
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI