TimeStamp Formatting

[dcguuenther]

I am having trouble formatting the timestamp in the manner I need.

In the database, the timestamp is being formatted as

%yyyy-%mm-%dd %hh:%nn:%ss.6

THe datastage project default is

%yyyy-%mm-%dd %hh:%nn:%ss

I have tried overriding the timestamp format at the job level(Job properties/defaults tab) to read

%yyyy-%mm-%dd %hh:%nn:%ss.6 (I also tried %yyyy-%mm-%dd %hh:%nn:%ss.%6 and %yyyy-%mm-%dd %hh:%nn:%ss.xxxxxx) as the documentation suggests.

However, I don't feel that the override is working, because if I call a row generator to a transformer, and in the transformer specify current timestamp --- it gets created in the format %yyyy-%mm-%dd %hh:%nn:%ss without the 6 digits of microseconds after it that I have specified in the job defaults tab.

This causes me a problem because when I try to read the timestamp from the database Datastage blows up. My only other option that doesn't involve way too much coding would be to truncate the timestamp in the SQL itself --- though I don't feel I should have to do this.

Any ideas on how I succesfully bring this timestamp in without truncating and without datastage blowing up.

Thanks

[dcguuenther]

My problem actually ended up not being the format.

The issue is DataStage cannot handle the following timestamps

before 01-03-0001 :00.00.00

or after and including 12-31-9999 24:00:00

My problem was DB2 sets the timestamp to 12-31-9999 24:00:00 which one could argue is actually into the year 10,000. It also didn't like the date 12-31-9999 so I used a case statement to subtract 1 day in the query before bringing into Datastage. Take a close look at the values causing you problems. Datastage should not break because of a timestamp format, if anything it would truncate automatically, not abort. Thus its probably its lousy way of handling certain date and timestamp values.

[ArndW]

I beg to differ. DataStage doesn't care about dates internally, the problem you are having is when a date conversion is done to DB/2.

According to the docs, DB/2 cannot store a date beyond 12/31/9999; but it should store 01/01/0001. What happens if you have that minimum date and write to DB/2?

Regarding the job properties settings I think that this is a DataStage bug; I think I reported it several months back but do not recall the outcome or ticket number (it was part of a set of other issues so I think it got lost in the crush).

[kumar_s]

I guess, the extendable property of TimeStamps 'MicroSecond' can be enabled to sort this issue out. Else why can the project level of the timestamp in datastage be changed to the required timestamp with micro second.