String to timestamp conversion

satishm · Post by **satishm** » Wed Nov 04, 2009 12:46 pm

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.

kumar_s · Post by **kumar_s** » Wed Nov 04, 2009 1:24 pm

Could you provide the exact derivation that you used? With the exact input and output datatype.

satishm · Post by **satishm** » Wed Nov 04, 2009 1:29 pm

kumar_s wrote:Could you provide the exact derivation that you used? With the exact input and output datatype. ...

Hi Kumar,

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

kumar_s · Post by **kumar_s** » Wed Nov 04, 2009 1:34 pm

It doesn't match the input data format.
Strip out 'AM' part of the data (last two character) and providet value as "%yyyy%mm%dd%hh.%mm.%ss"

neena · Post by **neena** » Wed Nov 04, 2009 1:39 pm

When you bring the field try to change into "yyyy-mm-dd hh:nn:ss" format and use the function. I did a test job and it seems like the function is working only with the above format.

satishm · Post by **satishm** » Wed Nov 04, 2009 1:42 pm

neena wrote:When you bring the field try to change into "yyyy-mm-dd hh:nn:ss" format and use the function. I did a test job and it seems like the function is working only with the above format.

Neena,
Will you please share your derivation.
And how about if date time stamp is 'pm' ?

neena · Post by **neena** » Wed Nov 04, 2009 1:58 pm

you need to add more logic when it is PM. Like create a stage variable to check for PM and hour part.

Like

Stage vari: When string = 'PM' and hour part = '12' Then '12' Else If string = 'PM' Then '12+hour part[]' Else hour part

neena · Post by **neena** » Wed Nov 04, 2009 2:24 pm

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.

Kryt0n · Post by **Kryt0n** » Wed Nov 04, 2009 2:41 pm

The format you provide to StringToTimestamp needs to match your input format, not how you want it to look.

For am/pm use the %aa format option

Help files are more than enough to figure out the correct format

satishm · Post by **satishm** » Wed Nov 04, 2009 2:55 pm

Kryt0n wrote:The format you provide to StringToTimestamp needs to match your input format, not how you want it to look.

For am/pm use the %aa format option

Help files are more than enough to figure out the correct format

Thanks, Neena ...It worked.....God BlesS!!

chulett · Post by **chulett** » Wed Nov 04, 2009 2:58 pm

So you quoted the simple solution but actually implemented an unnecessarily complex one? If so, you might want to revisit this.

satishm · Post by **satishm** » Wed Nov 04, 2009 3:01 pm

chulett wrote:So you quoted the simple solution but actually implemented an unnecessarily complex one? If so, you might want to revisit this. ...

Sorry...I dint get u

chulett · Post by **chulett** » Wed Nov 04, 2009 3:05 pm

What solution did you use - all that extra work using stage variables or the proper StringToTimestamp function syntax that Kryt0n helped out with?

satishm · Post by **satishm** » Wed Nov 04, 2009 3:08 pm

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

One which Neena provided, as i dont wanna store am/pm in table and also wanna store it in 24 hrs format..
She provided a perfect solution.

But thanks to all for all the help.

satishm · Post by **satishm** » Wed Nov 04, 2009 3:16 pm

satishm wrote:
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? ...
One which Neena provided, as i dont wanna store am/pm in table and also wanna store it in 24 hrs format..
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.