Hi,
running db2 v8.2 fp9 on Windows when executing following SQL:
from DB2CMD SQL runs successfully.

But when I run the same SQL with ODBC driver I get error:
ODBC Layer Error: [01506] ==> [[IBM][CLI Driver][DB2/NT] SQLSTATE 01506: An adjustment was made to a DATE or TIMESTAMP value to correct an invalid date resulting from an arithmetic operation.

The most strange thing is this SQL is in production environment for more then one year and there was no problem at all.

I have temporally changed the SQL to remove "current date" and put fixed values like:
and this SQL works fine using ODBC connection.

It looks like there is some kind of bug... Is there any command to clear date registries or something like that. I have also tried to reboot computer and problem persists.

What should I do to solve the problem?
Regards,
Grofaty

Hi,
I have checked log files and found out that the same problem was on:
Dec 31 2007
Mar 31 2008
May 31 2008
Aug 30 2008
Aug 31 2008
and now
Oct 31 2008

Very interesting it looks like a bug in DB2 v8.2 FP9
Regards,
Grofaty

Did you apply the DST (daylight saving time) fixes on the DB2 server?

n_i, I am not from US, so this DST does not effects me.

I have found out interesting things. I have set my system time to 31.10.2008! to simulate the problem.
1. the following SQL returns error:
WHERE mydate_column BETWEEN CURRENT DATE - 6 MONTHS AND CURRENT DATE - 1 DAY

2. But the following SQL works fine:
WHERE mydate_column BETWEEN date('31.10.2008') - 6 months AND CURRENT DATE - 1 DAY

But all this problems are only using ODBC! The same SQL executed using DB2CLP is working without any problem.

It looks combination of 'current date' and 'months' produces some bug!

I have temporally solved the problem to write SQL like this:
"WHERE mydate_column BETWEEN CURRENT DATE - 180 DAYS AND CURRENT DATE - 1 DAY"

I know this SQL does not return exact the same number of rows, but I don't care, because start date requirement is not so strict - I just need 6 months of data to write graph trend diagram.

If checking the log file:
DEC 31 2007 - 6 MONTHS = JUN 31 2007
MAR 31 2008 - 6 MONTHS = SEP 31 2007
MAY 31 2008 - 6 MONTHS = NOV 31 2007
AUG 30 2008 - 6 MONTHS = FEB 30 2008
AUG 31 2008 - 6 MONTHS = FEB 31 2008
OCT 31 2008 - 6 MONTHS = APR 30 2008

All this resulting dates does not exists!!! So DB2 should return first existing date before this date. It looks like a "nasty" bug in ODBC in v8.2 FP9.

Hi,
I have tested the same SQLs through ODBC connection to DB2 v9.5 FP2a and it looks like IBM has already fixed this bug.
Regards