Insert Into DB2 table with Select from SQL Server

Question

I am backloading data from a SQL Server table into a DB2 table. I am getting the same error for all of my null date columns:

Operand type clash: int is incompatible with date

It seems to be caused by weird "null date is being treated as 0, maybe" behavior no matter if I use Openquery or LinkedServer. I am using a temp table with a date column named ACTUAL_CLOSED.

I cannot share the source directly. But I can recreate with foobar if absolutely necessary

Test cases below:

Case 1: column IS null in temp table --> FAIL
Case 2: I hardcode the insert value with null at insert and comment the original "null, --ACTUAL_CLOSED" --> SUCCESS
Case 3: I use COALESCE to force a "hard coded" null "COALESCE(ACTUAL_CLOSED, null)" --> FAIL
Case 4: I use IS_NULL to force a "hard coded" null "IS_NULL (ACTUAL_CLOSED, null)" --> FAIL
Case 5: I use a case statement and check for null AND 0 "case when ACTUAL_CLOSED = 0 or ACTUAL_CLOSED is null then null else ACTUAL_CLOSED end as 'ACTUAL_CLOSED"' --> FAIL
Case 6: I update the temp table with NULL after loading it before insert to DB2 --> FAIL

Any help understanding is appreciated

It would help if you edit your question to show the exact datatype and allows-null attribute for the columns in both the Db2-target-database and the SQL-server database.e.g ACTUAL_CLOSED DATE NOT NULL, or ACTUAL_CLOSED DATE, or ACTUAL_CLOSED INTEGER etc. Ideally they should exactly match , otherwise the values will need to be changed (and sanitized/conditioned if there is a type mismatch . — mao
– mao, Commented May 15 at 15:48
Also add platform and version of Db2. Legacy tables on Db2 for IBM i don't usually use actual date types. — Charles
– Charles, Commented May 15 at 15:58
maybe you could use a special date ('1111-11-11') as null in the temp table and use the case to make it null in the insert — Turo
– Turo, Commented May 15 at 17:19
Please read : Why should I provide a Minimal Reproducible Example, even for a very simple SQL query? — MatBailie
– MatBailie, Commented May 16 at 0:47

Dale K · Accepted Answer · 2025-05-16 10:41:46Z

0

This can happen if your script does an equivalent of:

INSERT INTO sometargettable (datecolumn...)
SELECT NULL..`

Because SQL Server has a default NULL cast to INT, which can mess things up if target column is a date.

You can usually solve it by doing:

INSERT INTO sometargettable (datecolumn...)
SELECT CAST(NULL AS DATE)

Which avoids incorrect upcasting.

edited May 16 at 10:41

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered May 16 at 9:39

siggemannen

10.1k3 gold badges12 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert Into DB2 table with Select from SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related