Inserting Oracle Date and Time data

ketfos · Post by **ketfos** » Wed Jan 05, 2005 6:36 pm

Hi,
Before you establish connection to datastage,
call sqlplus in shell script,
alter the session in shell script
and then call datastage job using dsjob in shell script.

Ketfos

davidnemirovsky · Post by **davidnemirovsky** » Wed Jan 05, 2005 7:00 pm

I would use the TO_DATE function personally, but if you can't you will have to use Iconv and Oconv:

Code: Select all

Oconv(Iconv(YourDate, "D-YMD[4,2,2]"), "D-DMY[2,A3,2]")

xcb · Post by **xcb** » Wed Jan 05, 2005 7:35 pm

You should be able to load an oracle date field directly from an OCI stage or an ODBC stage, as long as the date is formatted corectly eg. yyy-mm-dd hh24:mi:ss. These stages automatically put the dates in the TO_DATE function for you.

chulett · Post by **chulett** » Wed Jan 05, 2005 9:08 pm

Exactly!

Set the data type to Date or Timestamp and the Oracle stages will automatically use the appropriate TO_DATE function. That's why it's important to make sure the fields are properly formatted before they hit the output stages. BTW, as noted, only two digits for the seconds are allowed in an Oracle DATE field.

It's all in the pdf documentation for the various stages, you'll find them in the 'Docs' directory under your client install directory.