time stamp conversion

[pradeepleon]

Check for the OCONV() function in the help topics.

[sud]

Check for the OCONV() function in the help topics.

Parallel jobs, no oconv. Well, you have to actually check this, the timestamp format within datastage IS in 24 hr format, so you should not have to do anything special.

[ray.wurlod]

There is no Oconv() function in the parallel Transformer stage.

Convert the timestamp to a string, and substring off the am/pm designator (Right() function is ideal).
Substring out the hours field, convert to int8 and, if the designator is PM add 12. Convert back to string.
Use concatenation to reassemble the parts, then convert back to timestamp.

Use stage variables for intermediate results - it will make future maintenance much easier.

[pradeepleon]

Check for the OCONV() function in the help topics.

Parallel jobs, no oconv. Well, you have to actually check this, the timestamp format within datastage IS in 24 hr format, so you should not have to do anything special.

Oh I'm sorry, I thought it to be for a server job.

[samsuf2002]

THANKS TO ALL . That field is coming as char (26) , can any one please tell me what function i can use for this condition .

[DSguru2B]

Use the following in Modify stage.

Code: Select all

date_fmt:timestamp = timestamp_from_string [%mmm %dd %yyyy %hh:%nn:%ss]  (date_tst) 

But in order for this to work, you will have to pre process your date to have two digits for day.
To get the scale of 3, in your target stage, set the default timestamp format to %yyyy-%mm-%dd %hh:%nn:%ss.6

[samsuf2002]

I have around 5 time stamp fields where should i specify their names in the code provided. i want to keep the same field name .

Thanks

[DSguru2B]

O yea, forgot to mention that.
date_fmt is your source column name and
date_tst is your target column name. They can be same too, I think.

[samsuf2002]

i am new to modify stage , i gave 5 specification with that code and changed the format to %yyyy-%mm-%dd %hh:%nn:%ss.6 , i am getting this error

Un-handled conversion error on field "UMIT_STS_DTM " from source type "string[26]" to destination type "timestamp":
source value="Jul 9 1998 12:01:51:346PM"; the result is non-nullable and there is no handle_null to specify a default value

how do i pre process that date to have two digits for day?

[DSguru2B]

If you are comfortable with the transformer then you can preprocess the date in a stage variable to get two digit day. Something like the following:

Specify a stage variable, say FixDay. Its derivation will be

Code: Select all

If Len(Field(in.Col, " ", 2)) = 1 then Field(in.Col, " ", 1):' 0':Field(in.Col, " ", 2,3) else in.Col

Now in the derivation use

Code: Select all

StringToTimestamp(FixDay, "%mmm %dd %yyyy %hh:%nn:%ss")

Now your target is not nullable. Hence you need to wrap the last derivation into NullToValue() and provide an appropriate value if null is encountered.

[samsuf2002]

when i am trying to run by using the above code its giving me this warning

Transformer_6,0: Conversion error calling conversion routine timestamp_from_string data may have been lost

and data is coming as all *s , my date is like this

"Jul 6 1998 12:00:00:000AM" after Jul there are 2 spaces.

[DSguru2B]

Do you have two spaces between the Month and Day in all your date fields or just a few?

[samsuf2002]

if the date is Jul 7 ----it has 2 spaces
if the date is Jul 12 -----it has 1 space , i am testing this with seq --transformer---seq.

later i can add modify stage ,what data type should i use for the above job char ot time stamp?

[us1aslam1us]

Read it as varchar from source stage. Have you tried DSGuru's routine ?

[DSguru2B]

Ok, then change the FixDay stage variable's derivation to the following:

Code: Select all

If in.Col[5,1] = " " then in.Col[1,3]:" 0":in.Col[6,21] else in.Col

The StringToTimestamp() derivation will remain the same. Read the date as varchar.