minutes data lost in transfer of Oracle 9i dates

jlegare · Post by **jlegare** » Sat Feb 21, 2004 4:30 pm

Taking DATE data from Oracle 9i and passing it to a sequential file with no transformation other than passing through a transform that merely maps fields - no real transformation.

A DATE column in Oracle has a date/time of 2004-02-21 00:07:00. It is being passed to a sequential file and is written as 2004-02-21 00:00:00

Notice that the minutes are lost.

In the passive stages for Oracle extract and sequential file write the column SQL Type is specified as Timestamp in both cases.

If I send the Oracle date column mentioned above to another Oracle date column the date is properly recorded as 2004-02-21 00:07:00.

Any suggestions?

Thanks

J.

ray.wurlod · Post by **ray.wurlod** » Sat Feb 21, 2004 5:27 pm

Can you post the generated SELECT statement? This may offer some insight into what is happening.
Can you try using user-defined SQL, specifying the column as CHAR or VARCHAR, and using a TO_CHAR function to effect conversion, using the date picture of your choice (for example 'YYYY-MM-DD HH24:MI:SS')?

jlegare · Post by **jlegare** » Sat Feb 21, 2004 6:09 pm

Thanks for taking an interest.

I'll try your suggestions.

Here is an extract of the sql: SELECT ... TO_CHAR(CUST_ORDER_DT, 'YYYY-MM-DD HH24:MI:SS') ... FROM TABLE ...

As you can see nothing unusual here.

The write sql is INSERT INTTO ... TO_DATE(:9, 'YYYY-MM-DD HH24:MI:SS')

Nothing unusual here either.

[quote="ray.wurlod"]Can you post the generated SELECT statement? This may offer some insight into what is happening.
Can you try using user-defined SQL, specifying the column as CHAR or VARCHAR, and using a TO_CHAR function to effect conversion, using the date picture of your choice (for example 'YYYY-MM-DD HH24:MI:SS')?[/quote]

jlegare · Post by **jlegare** » Sat Feb 21, 2004 6:40 pm

The problem was solved by droping the sequential file stage and recreating it from scratch.

This is version7.0. Perhaps this is to be expected.

J.

chulett · Post by **chulett** » Sat Feb 21, 2004 8:48 pm

If you literally mean you are running version 7.0 then it would behoove you to update to 7.0.1 which is available for all platforms. There are quite a number of fixes in it and I'm pretty sure the list was posted over at ADN when it was announced as 'Generally Available'.