String to timestamp conversion
Moderators: chulett, rschirm, roy
String to timestamp conversion
Hi Friends,
Need a help on date time stamp conversion.
I recieve a timestamp of format which will be read in a CFF stage using cobol definition :
2009091909.25.03 am (yyyymmddhh.mm.ss am) - 12 hours format.
Bytes:19
Data type: Character
Now I want to convert it in to standard date time stamp 2009-09-19 09.25.03
I used coversion format string to timestamp in transformer stage but it outputed strange (***************)
Appreciate your help.
Regards,
Satish.
Need a help on date time stamp conversion.
I recieve a timestamp of format which will be read in a CFF stage using cobol definition :
2009091909.25.03 am (yyyymmddhh.mm.ss am) - 12 hours format.
Bytes:19
Data type: Character
Now I want to convert it in to standard date time stamp 2009-09-19 09.25.03
I used coversion format string to timestamp in transformer stage but it outputed strange (***************)
Appreciate your help.
Regards,
Satish.
Hi Kumar,kumar_s wrote:Could you provide the exact derivation that you used? With the exact input and output datatype. ...
Please find the exact derivation and datatype of input and output respectively
StringToTimestamp(DSLink4.time,"%yyyy-%mm-%dd %hh:%nn:%ss")
Input:
19 bytes Char
Output:
26 Bytes, scale 0f 6 Timestamp
Regards,
Satish
Stage Var1: String[18,2]
Stage Var2: When Stage Var2 = 'PM' and string[9,2] = '12' Then '12' Else If Stage Var2 = 'PM' Then '12+string[9,2]' Else string[9,2]
Stage var3: String[1,4]:'-':string[5,2]:'-':string[7,2]: ' ':Stage Var2: String[11,6]
Column derivarion: StringToTimestamp(Stage var3,"%yyyy-%mm-%dd %hh:%nn:%ss")
Replace string with your field name.
Stage Var2: When Stage Var2 = 'PM' and string[9,2] = '12' Then '12' Else If Stage Var2 = 'PM' Then '12+string[9,2]' Else string[9,2]
Stage var3: String[1,4]:'-':string[5,2]:'-':string[7,2]: ' ':Stage Var2: String[11,6]
Column derivarion: StringToTimestamp(Stage var3,"%yyyy-%mm-%dd %hh:%nn:%ss")
Replace string with your field name.
One which Neena provided, as i dont wanna store am/pm in table and also wanna store it in 24 hrs format..chulett wrote:What solution did you use - all that extra work using stage variables or the proper StringToTimestamp function syntax that Kryt0n helped out with? ...
She provided a perfect solution.
But thanks to all for all the help.
satishm wrote:One which Neena provided, as i dont wanna store am/pm in table and also wanna store it in 24 hrs format..chulett wrote:What solution did you use - all that extra work using stage variables or the proper StringToTimestamp function syntax that Kryt0n helped out with? ...
She provided a perfect solution.
But thanks to all for all the help.
Ran the job using
StringToTimestamp(DSLink4.time,"%yyyy%mm%dd %hh.%nn.%ss %aa")
got aborted with following error
APT_CombinedOperatorController,0: Unrecognized timestamp format identifier: %97
APT_CombinedOperatorController,0: Caught exception from runLocally(): APT_ParseError: Parsing parameters "%yyyy%mm%dd %hh.%nn.%ss %aa" for conversion "timestamp=timestamp_from_string[%yyyy-%mm-%dd %hh:%nn:%ss](string)": APT_Conversion_String_TimeStamp: Invalid Format [%yyyy%mm%dd %hh.%nn.%ss %aa] used for string_from_time type conversion.
Operator signalled one or more errors.