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.
minutes data lost in transfer of Oracle 9i dates
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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')?
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')?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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]
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]
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'.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers