DSXchange

Hi,

Please let me know how to convert the following Timestamp to standard Oracle Timestamp format YYYY-MM-DD HH24:MM:SS.

2013-05-28 05:09:03.330000

Is the below correct one.

StringToTimestamp(Ln_Transform.HeadcountDate,"%yyyy-%mm-%dd %hh:%nn:%ss.%6")

Iam getting the below error

Tr_Transform,1: Operator terminated abnormally: Terminating with exception:APT_ParseError: Parsing parameters "%yyyy-%mm-%dd %hh:%nn:%ss.%6" for conversion "timestamp=timestamp_from_ustring[%yyyy-%mm-%dd %hh:%nn:%ss](ustring)": APT_Conversion_String_TimeStamp: Invalid format [%yyyy-%mm-%dd %hh:%nn:%ss.%6] used for string_from_time type conversion


Thanks,

First thing to note is your desired format posted does not include microseconds - are you targeting a TIMESTAMP column in Oracle that will support them or are you actually writing to a DATE?

Assuming the former, if you check the documentation you'll see your issue is the format string you are using in the conversion function:

For a timestamp, the format is %yyyy-%mm-%dd %hh:%nn:%ss. If extended to include microseconds, the format is %yyyy-%mm-%dd %hh:%nn:%ss.x, where x gives the number of decimal places seconds is given to.

That is, you don't put a "%" in front of the 6. Why not take the leftmost 19 characters of the string and convert that (actually it will already be in the correct format for standard timestamp)?

Thanks, will try and get back,