IBMDBDB2 OLEDB provider does not appear to like case

[JAW]

I have sucessfully created an SQL SERVER LINKED SERVER to point to DB2
on Z/OS.
I have been successully able to copy most of the tables in the
database. However, I have a couple that have a dates outside of SQL
Server supported range.

IBMDADB2 at 8.1 FIXPAK 10.

I can run the OPENQUERY() component on the mainframe and it works fine
except when using CASE. I can CAST various datatypes fine (From
TIMESTAMP to CHAR(26) for instance)
TRUNCATE TABLE METER_PT
INSERT INTO METER_PT
SELECT KY_PREM_NO, KY_SPT, KY_MPT_NO, KY_MTR_EQUIP_NO,
CD_MTR_EQUIP_MFGR,DT_EFF, CD_MTR_STAT, CD_MPT_STAT, CD_STAT,
CD_MPT_USE, QY_MTR_MULT, QY_NO_OF_DIAL
FROM OPENQUERY(HOST,'
SELECT KY_PREM_NO, KY_SPT, KY_MPT_NO, KY_MTR_EQUIP_NO,
CD_MTR_EQUIP_MFGR,
CASE WHEN DT_EFF < '1800-01-01' THEN NULL ELSE DT_EFF END DT_EFF,
CD_MTR_STAT, CD_MPT_STAT, CD_STAT,
CD_MPT_USE, QY_MTR_MULT, QY_NO_OF_DIAL
FROM DB2X.XXXXX.METER_PT
')
GO

[JAW]

CASE WHEN DT_EFF < '1800-01-01' THEN NULL

should have been

CASE WHEN DT_EFF < ''1800-01-01'' THEN NULL

Its all good!
JAW wrote:

I have sucessfully created an SQL SERVER LINKED SERVER to point to DB2
on Z/OS.
I have been successully able to copy most of the tables in the
database. However, I have a couple that have a dates outside of SQL
Server supported range.

IBMDADB2 at 8.1 FIXPAK 10.

I can run the OPENQUERY() component on the mainframe and it works fine
except when using CASE. I can CAST various datatypes fine (From
TIMESTAMP to CHAR(26) for instance)
TRUNCATE TABLE METER_PT
INSERT INTO METER_PT
SELECT KY_PREM_NO, KY_SPT, KY_MPT_NO, KY_MTR_EQUIP_NO,
CD_MTR_EQUIP_MFGR,DT_EFF, CD_MTR_STAT, CD_MPT_STAT, CD_STAT,
CD_MPT_USE, QY_MTR_MULT, QY_NO_OF_DIAL
FROM OPENQUERY(HOST,'
SELECT KY_PREM_NO, KY_SPT, KY_MPT_NO, KY_MTR_EQUIP_NO,
CD_MTR_EQUIP_MFGR,
CASE WHEN DT_EFF < '1800-01-01' THEN NULL ELSE DT_EFF END DT_EFF,
CD_MTR_STAT, CD_MPT_STAT, CD_STAT,
CD_MPT_USE, QY_MTR_MULT, QY_NO_OF_DIAL
FROM DB2X.XXXXX.METER_PT
')
GO