Modification of the Timestamp format with PX

[Peytot]

Hi,

I can't find the solution, that's why I need your help.

My input is a file and the format of my timestamps is:
"6/1/2002 01:00:00.000 PM"

I need to transform it to the following format:
"2002-01-06 13:00:00"

How can I do this with PX?

Thank you,

Pey

[ArndW]

Hello Peyote (sorry, I couldn't resist),

You wouldn't happen to be on the same project as me, in Nice? We are doing the exact same thing today so I was wondering...

Anyway, the Tx stage function that will do this is:

StringToTimestamp(InDate,"%yyyy-%dd-%mm %hh:%nn:%ss")

[Peytot]

Sorry ArndW. I come from Lyon but I have a mandate in Canada.

Good word game, I never thought it.

Concerning your answer, I try it but in my Output, I have stars in place of the timestamp.
In input, I have the value "6/1/2002 01:00:00.000 PM" defined as Varchar(25)
and in output, I have "*******************" defined as Varchar or Timestamp (I tried both).

Have you have any suggestion to resolve this?

Thank you for your time.

Pey.

[ArndW]

Bonjour Peytot,

try writing to a PEEK or flat file and seeing if the value is written correctly, if YES then the answer is in the OCI stage & conversion, if not then the Px isn't parsing the date correctly. I use that function on incoming string data without the time portion and it works flawlessly.

[Peytot]

Arnd,

The result I have is in putting the result into a flat file. I will investigate better your point today.

Thank you

[Eric]

You can set the input (Or Output) column format in the PX Sequential file stage.
If you define the required column as Type TimeStamp (rather than a Varchar) then on the format tab (SEQ file stage) set the property for [TimeStage->FormatString]

[Eric]

Then again this might have problems with the 'PM' part of the data.
How about reading the data as a Varchar and then using a Transformer to apply the function StringToTimestamp(InDate,"%yyyy-%dd-%mm %hh:%nn:%ss") as noted by ArndW.

[memrinal]

Hi i was also doing something similar and am getting "*"'s in result field. Please let me know what should be input type amd length and in which format will this functaion accept date. Also what should be output type and lrngth.
Thanks in advance

[Peytot]

Hi,
the signification of the stars is the PX StringToTimestamp function cannot interpret the timestamp input format that I'm trying to convert.

We have to found the good format if it exists with PX else to use a Server job.

Regards,

Pey

[dsxuserrio]

Hi
I am not sure if you will like it. But this is what we are doing. And it works great. Yes we do process huge volumes of data.

We are using transformer's substring operations. Yes. Basically rip the 25 char string and build a sring of different format.

By any chance can you get your input in your desired format from your source system?? Doesn't hurt to ask your upstream folks.
Thanks

[T42]

Do not forget to include the Extended option for Timestamp to include Microseconds.