Best way to insert Timestamp String into Oracle Date?

MrBlack · Post by **MrBlack** » Thu Jul 02, 2015 8:53 am

With the new Oracle Connectors, I can't see the Insert/Update generated statement like I could with the old OCI stage. How do I know how to get a timestamp string from a CSV file to match the format mask?

Here's the format of the timestamp string

Code: Select all

YYYY-MM-DD hh:mi AM

The column is an Oracle DATE but in Datastage I set the column datatype to TIMESTAMP. The NLS settings in Oracle are

Code: Select all

NLS_DATE_FORMAT		DD-MON-RR
NLS_TIMESTAMP_FORMAT	DD-MON-RR HH.MI.SSXFF AM

When I don't try to manipulate the string at all, Datastage doesn't give me any errors but the AM/PM information is ignored and all my data is inserted as AM and I lose that PM information. Should I try and change my time into a 24hr format?

Surely there has to be an easy way to keep the genreated SQL but also be able to get my data inserted properly.

MrBlack · Post by **MrBlack** » Thu Jul 02, 2015 9:46 am

This is what I have discovered. Unless there is a SECOND component to the string data the AM/PM is ignored. Also in ordered to get it to work, I had to convert from a 12 hr time to a 24 hour time. So final steps to resolution, concat 00 seconds to my string, and then convert to 24hrs.

Very annoying bug.