Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: ODBC error: n adjustment was made to a DATE or TIMESTAMP value to correct an invalid

    Hi,
    running db2 v8.2 fp9 on Windows when executing following SQL:
    Code:
    select
    col1, col2
    from
    mytable
    WHERE mydate_column BETWEEN CURRENT DATE - 6 MONTHS AND CURRENT DATE - 1 DAY
    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:
    Code:
    select
    col1, col2
    from
    mytable
    WHERE mydate_column BETWEEN '30.04.2008' AND '30.10.2008'
    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
    Last edited by grofaty; 10-31-08 at 17:56.

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    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

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Did you apply the DST (daylight saving time) fixes on the DB2 server?
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Jan 2003
    Posts
    1,605
    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.

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by 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
    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.

  6. #6
    Join Date
    Jan 2003
    Posts
    1,605
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •