Non-numeric character in datetime or interval?

[andrewkshin]

I am doing a simple load to an informix database from a flat file. My flat file has a DATE field in the format mm/dd/yyyy.

When I try loading it into my informix client database, I get the following error:

Code: Select all

[DataDirect][ODBC Informix driver][Informix]Non-numeric character in datetime or interval.

Then DataStage makes the field NULL and the row is rejected from the database. Can anyone help me?

Thank you! :D

[edesyatnik]

I've been struggling with the same problem, but in Oracle.

Actually, my source flat file is even using the same format!
(MM/DD/YYYY) My target column is type DATE; DataStage
always inserts a NULL instead of the value..

I've tried changing the metadata of the source column to a
varchar, I've tried setting the target as Timestamp or Date,
hoping DataStage would be smart enough to figure it out.

Then I tried BASIC date conversion functions -- in this case,
OConv(IConv( InXmap.ORACLE_DATE_COLUMN,"D-DMY"),
"D-YMD[4,2,2]") and OConv(IConv( InXmap.ORACLE_DATE,
"D/E"), "D-YMD[4,2,2]") -- didn't work for me, but you may
want to give it a try. Mine continued to insert NULL.

In the end, I just used a staging table in Oracle with all the
columns being of type VarChar2() -- then I just used a custom
SQL statement to perform the final conversion. In the case
of Oracle, it was to_date(STATING_COLUMN,'MM/DD/YYYY')
with the result being inserted in my final table.

[chulett]

Yerk. Can't help with the DB2 problem, but can with the Oracle one.

You really need to read the pdf docs for the OCI plugin, it explains what format each datatype is expecting in the stage. Dates are expected to be handled as a Timestamp data type and should be in the following format:

YYYY-MM-DD HH24:MI:SS

For your date where you don't need a valid time, simply append " 00:00:00" to the end of your date. Custom routines or transforms can ensure you are handling this in a standard / consistant manner in all jobs.