Page 1 of 1

Oracle date format

Posted: Fri Aug 01, 2003 9:34 am
by johnno
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.

Posted: Fri Aug 01, 2003 9:47 am
by tonystark622
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

Posted: Fri Aug 01, 2003 9:59 am
by kduke
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

Posted: Fri Aug 01, 2003 10:10 am
by johnno
Thanks very much Tony - it works!