Inserting a TimeStamp Value into Oracle Database

[vivek_rs]

Hi
I am unable to insert a Timestamp DataType value into and Oracle 9i database. I'm using an ODBC Stage with User Defined SQL as the Update Action.

Code: Select all

lookup..Transformer_11.DSLink13: DSD.BCIPut call to SQLExecute failed.
SQL statement:INSERT INTO COMET_ETL.CHECKDATETIME(ID, CITY, CURRENTDATE) VALUES (?,?,TO_DATE(?, 'MM-DD-YYYY HH24:MI:SS')) 
SQLSTATE=22008, DBMS.CODE=0
[DataStage][SQL Client][ODBC][DataDirect][ODBC Oracle Wire Protocol driver]Datetime field overflow.  Error in parameter 3.

ID = "1"
CITY = "2"
CURRENTDATE = "12-09-2004 17:51:29"

Please help
TIA

[bakul]

Instead of using TO_DATE(?, 'MM-DD-YYYY HH24:MI:SS') in the user-defined query,you could generate the timestamp in the transformer using,
OConv(@DATE,"D-YMD[4,2,2]"):'-':Oconv(@TIME,"MTS."):'.':'000000'

Define the timestamp field as timestamp with length of 26 and scale 6
Now directly use this timestamp in the insert query.

[vivek_rs]

That helped a lot Bakul
Thanks a lot!

[changming]

the datastage can not recognise the date foramt(dd/mm/yyyy)
use oconv and iconv to format the date to yyyy-mm-dd then you can load date. the delimeter symbol is not important.

[ray.wurlod]

DataStage can "recognize" any date format, because it does not have any data types - everything is a character string.

It's the database servers and/or their client software (such as ODBC drivers) that grumble about date formats - not DataStage!