Insert throws SQLCODE: -180, SQLSTATE: 22007 on IBM DB2

user2087444 picture user2087444 · Feb 19, 2013 · Viewed 50.1k times · Source

I'm using a data integration tool (Talend) to insert some rows on a IBM Db2, but the following command throws an error:

INSERT INTO "TOTCHQ" ("CODREM","NUMDOCC8","NOMPES","NUMBAN","CODAGEBN","QTDCHQDL","DATULTOA") VALUES ('3080',99999999999,'FULANO DE TAL',100,'0000',2,'2012-11-28')

DB2 SQL error: SQLCODE: -180, SQLSTATE: 22007, SQLERRMC: null - Line: 0

Seems like the date column (DATULTOA) is in wrong format, but it isn't.

The same command, when executed on another tool (like DbVisualizer) or even directly, runs ok.

Here is my table metadata:

CODREM      VARCHAR(4)     
NUMDOCC8    DECIMAL(14, 0)
NOMPES      VARCHAR(50) 
NUMBAN      SMALLINT  
CODAGEBN    VARCHAR(6)
QTDCHQDL    SMALLINT    
DATULTOA    DATE 

Thanks in advance.

Answer

Gilbert Le Blanc picture Gilbert Le Blanc · Feb 19, 2013

The description of SQLCODE -180 is "THE DATE, TIME, OR TIMESTAMP VALUE value IS INVALID".

I count 7 column names and 7 data values. So, it's not a misalignment problem.

The date '2012-11-28' assumes a yyyy-mm-dd format. Perhaps talend is expecting a yyyy-dd-mm format, or some other date format.