String to Timestamp Conversion

[Madhusudhan]

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

[BI-RMA]

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.

[crystal_pup]

Try using Oconv,Iconv functions

[BI-RMA]

In Enterprise Edition (PX)?

[crystal_pup]

In case if "User Variable Activity" is used in a sequence then Oconv,Iconv functions can be used

[BI-RMA]

Of course, but this is only for passing a parameter to a job. Does not look like that to me in the example...

[priyadarshikunal]

Unless the OP is on 9.1 as 9.x is not added in the drop down. StringToTimestamp() should do the task.

[chulett]

They've specified 8.1 in the Additional Info field. A recent discussion of the function and that format is here.

[Madhusudhan]

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

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)

derivation:

Code: Select all

StringToTimestamp(StageVar,"%dd-%mmm-%yyyy %(H,s).%nn.%ss %(aa,w)")

[priyadarshikunal]

I think there is a environment variable something like cutoff year, if set correctly takes care of adding 19 or 20 in the year.

[chulett]

Open up your Parallel Job Developer's Guide and search for "year_cutoff", it is part of the format mask and will get rid of all those if-then-else shenanigans.