Hi there,
I have a job which reads data from a comma-delimitted sequential file into an oracle table. One of the columns is a date value(format is mm/dd/yyyy ie.1/30/2003).
Once the data has been inserted into oracle from DataStage, however,
the dates get mangled up (like was: 1/30/2003 now:1/3/2030).
Anyone ever experience anything like this?
Thanks,
Mark
Date format
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Oracle can be funny with dates, depending among other things on the default date picture.
Similarly DataStage can be funny with dates, depending primarily on how the date is defined in metadata, both data element and SQL data type.
Various approaches exist, the most common being to use user-defined SQL (with a TO_DATE function) or to specify TimeStamp as the DataStage SQL data type, which generates the TO_DATE function.
Check the archives for David Barham's erudite posts on this issue.
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
Similarly DataStage can be funny with dates, depending primarily on how the date is defined in metadata, both data element and SQL data type.
Various approaches exist, the most common being to use user-defined SQL (with a TO_DATE function) or to specify TimeStamp as the DataStage SQL data type, which generates the TO_DATE function.
Check the archives for David Barham's erudite posts on this issue.
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Provided, of course, that you have a date that is in a format acceptable as a Generic date according to the SDK. Most "natural" dates (those acceptable for your locale) fall into this category; however, in a mixed locale environment (for example some DMY dates and some MDY dates) a little more conversion is required.
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518