DSXchange

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

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

Hi,

I've found that the following function works fine in the transformer stage if you want to insert a date into Oracle:

DateGenericToODBC(DSLink9.movementDate)

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