Modification of the Timestamp format with PX

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
Peytot
Participant
Posts: 145
Joined: Wed Jun 04, 2003 7:56 am
Location: France

Modification of the Timestamp format with PX

Post by 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" :twisted:

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

How can I do this with PX?

Thank you,

Pey
ArndW
Participant
Posts: 16318
Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:

Post by 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
Participant
Posts: 145
Joined: Wed Jun 04, 2003 7:56 am
Location: France

Post by 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
Participant
Posts: 16318
Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:

Post by 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
Participant
Posts: 145
Joined: Wed Jun 04, 2003 7:56 am
Location: France

Post by Peytot »

Arnd,

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

Thank you
Eric
Participant
Posts: 254
Joined: Mon Sep 29, 2003 4:35 am

Post by 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
Participant
Posts: 254
Joined: Mon Sep 29, 2003 4:35 am

Post by 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
Participant
Posts: 74
Joined: Wed Nov 24, 2004 9:13 pm

Post by 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
Mrinal Kumar

Even the IMPOSSIBLE says "I M Possible"
Peytot
Participant
Posts: 145
Joined: Wed Jun 04, 2003 7:56 am
Location: France

Post by 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
Participant
Posts: 82
Joined: Thu Dec 02, 2004 10:27 pm
Location: INDIA

Post by 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
dsxuserrio

Kannan.N
Bangalore,INDIA
T42
Participant
Posts: 499
Joined: Thu Nov 11, 2004 6:45 pm

Post by T42 »

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