Timestamp Converstion

[srini.dw]

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,

[chulett]

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.

[ray.wurlod]

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)?

[srini.dw]

Thanks, will try and get back,