Date format

sweetleaf · Post by **sweetleaf** » Wed Feb 05, 2003 3:58 pm

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

ray.wurlod · Post by **ray.wurlod** » Wed Feb 05, 2003 8:26 pm

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

sweetleaf · Post by **sweetleaf** » Fri Feb 07, 2003 3:27 pm

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)

ray.wurlod · Post by **ray.wurlod** » Fri Feb 07, 2003 3:48 pm

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