Timestamp value automatically getting formatted

divine · Post by **divine** » Wed Oct 10, 2007 5:40 am

When I populate a timestamp field to the table with value
2007-01-04 10:58:55.263000 it is getting inserted fine.

But, when I read the same record in next job using an OCI stage, it comes out as 2007-01-04 10:58:55.000000

Here if you can see the microsecond value .263000 is automatically getting converted to .000000

can you explain why?

DSguru2B · Post by **DSguru2B** » Wed Oct 10, 2007 11:43 am

I think this is a limitation. The database accepts timestamp upto 2 digit seconds. It does not like milliseconds. Try doing a command line insert with your data and see if that works.

Raftsman · Post by **Raftsman** » Wed Oct 10, 2007 1:50 pm

The timestamp datatype has an extended parm in the column layout that can be set to milliseconds. DataStage accept millisecond but cannot create them.

divine · Post by **divine** » Wed Oct 10, 2007 11:20 pm

DSguru2B wrote:I think this is a limitation. The database accepts timestamp upto 2 digit seconds. It does not like milliseconds. Try doing a command line insert with your data and see if that works.

The database is Oracle 10g. Now when I am inserting the timestamp value, it is getting populated fine with all timestamp miliseconds intact. While I am reading it in DS job using an OCI stage, the milliseconds are comming as all zeros.

I've mentioned timestamp extended as milliseconds. This is crucial, if its a bug as I need to use this field in change capture stage and if date will keep getting formatted like this, I'll always be getting updates and inserts

ray.wurlod · Post by **ray.wurlod** » Thu Oct 11, 2007 1:38 am

Eschew the OCI stage if this is critical - the OCI stage doesn't do microseconds properly. Prefer Oracle Enterprise stage.