string to timestamp with AM/PM

rameshkm · Posted: Mon Mar 28, 2011 9:18 pm

Hi,

I have a string value as "Jan 22 2010 2:12:44:201PM", i want to convert this string value to timestamp like 2010-01-22 14:12:44:201, is there any short way to handle this ? mean minimum using of stage variable.

Thanks

jwiles · Posted: Mon Mar 28, 2011 10:16 pm

Is the :201 portion of the time supposed to be milliseconds? Assuming so:

Convert to a timestamp by using StringToTimestamp() with a format specifier of "%mmm %d %yyyy %H:%n:%s:%SSS%aa"

When you're ready to output to a text field, or a flat file, use TimestampToString() with a format specifier of "%yyyy-%mm-%dd %hh:%nn:%ss:%SSS"

You may have to play around with the format specifiers a little bit...they are documented in the Parallel Job Developer Guide.

Regards,

rameshkm · Posted: Wed Mar 30, 2011 8:58 pm

I will try it and let you know.

rameshkm · Posted: Thu Mar 31, 2011 11:43 am

Hi

Please find the error below, while i tried to use your approach

--------------------------------------------------------------------------------
XFR_In_Trans_1,0: Caught exception from runLocally(): APT_ParseError: Parsing parameters "%mmm %d %yyyy %H:%n%s:%(SSS,v)%aa" for conversion "timestamp=timestamp_from_string[%yyyy-%mm-%dd %hh:%nn:%ss](string)": APT_Conversion_String_TimeStamp: Invalid format [%mmm %d %yyyy %H:%n%s:%(SSS,v)%aa] used for string_from_time type conversion.
--------------------------------------------------------------------------------

Please help in this, whether I have change any default Timestamp String format ?

jwiles · Posted: Thu Mar 31, 2011 11:34 pm

You're missing the colon between %n and %s in the time portion of the format string. For some reason, the format validator doesn't like it.

Regards,

rameshkm · Posted: Fri Apr 01, 2011 9:27 am

Hi Friend,

Its working fine , i used the below format to achive this

StringToTimestamp(Column1,"%mmm %(d,s) %yyyy %(H,s):%n:%s:%SSS%(aa,w)")

, thank you so much, i never forget this great help

vamsi.4a6 · Participant Joined: 22 Jan 2012 Posts: 233 Points: 1998

Could anyone explain description about following format specifiers?

1)%(d,s)
2)%(H,s)
3)%(aa,w)

chulett · Posted: Tue May 01, 2012 6:34 am

vamsi.4a6 wrote:

Could anyone explain description about following format specifiers?

jwiles wrote:

You may have to play around with the format specifiers a little bit...they are documented in the Parallel Job Developer Guide.

Oddly enough, in a section titled "Date and time formats". Wink