DSXchange

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

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.

Try using Oconv,Iconv functions

In Enterprise Edition (PX)?

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

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

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

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

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:

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

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.