Oracle date format

johnno · Post by **johnno** » Fri Aug 01, 2003 9:34 am

Using DataStage version 6 and I have no experience with Oracle databases.

I am trying to insert a row in an Oracle table VIA AN ODBC stage. The Oracle table has a column with a 'date' format. How do I format the date field so that the insert works? I am currently getting the following message:

[DataStage][SQL Client][ODBC][DataDirect][ODBC Oracle driver]Datetime field overflow. Error in parameter 7.

Where parameter 7 is:

TRANSMISSION_DATE="20030521"

Any help greatly appreciated.

tonystark622 · Post by **tonystark622** » Fri Aug 01, 2003 9:47 am

I just looked at the 'DateTimeStampToOraOCI' routine under Routines/SDK/Date in the repository. It looks like it takes a date in the format 'YYYY-MM-DD HH:MM:SS'. This routine uses ' 00:00:00' for the time part...

Good Luck,
Tony

kduke · Post by **kduke** » Fri Aug 01, 2003 9:59 am

Tony

I think he wants to know how to format a date to 'YYYY-MM-DD HH:MM:SS'. It looks like your date is coming in as a string. I would create a stage variable and initialize it to:

DateOut = TRANSMISSION_DATE[1,4]:"-":TRANSMISSION_DATE[5,2]:"-":TRANSMISSION_DATE[7,2]:" 00:00:00"

The [] is the same as the substring() function. Try that.

Kim.

Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com

johnno · Post by **johnno** » Fri Aug 01, 2003 10:10 am

Thanks very much Tony - it works!