Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2009
    Posts
    42

    Unanswered: incorrect results with DAYS

    If the number of DAYS I'm adding is equal or greater than the number of days in the month, then I get incorrect results.


    Correct:


    select CURRENT DATE + 29 DAYS - CURRENT DATE FROM SYSIBM.SYSDUMMY1

    1
    ----------
    29.



    Incorrect:

    select CURRENT DATE + 30 DAYS - CURRENT DATE FROM SYSIBM.SYSDUMMY1

    1
    ----------
    100.



    What am I doing wrong?

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Maybe it is a bug. But if you need it to work, try these:

    select days (CURRENT DATE + 29 DAYS) - days (CURRENT DATE) FROM SYSIBM.SYSDUMMY1

    1
    -----------
    29


    select days (CURRENT DATE + 30 DAYS) - days (CURRENT DATE) FROM SYSIBM.SYSDUMMY1

    1
    -----------
    30
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Incorrect:

    select CURRENT DATE + 30 DAYS - CURRENT DATE FROM SYSIBM.SYSDUMMY1

    1
    ----------
    100.
    No!
    This is what is expected.
    It means 1 month 0 day.

    SQL Reference Volume 1 ---> Chapter 2. Language elements ---> Expressions ---> Datetime operations and durations ---> Date arithmetic:
    The result of subtracting one date (DATE2) from another (DATE1) is a date duration that specifies the number of years, months, and days between the two dates. The data type of the result is DECIMAL(8,0).

    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT CURRENT DATE
         , CURRENT DATE + 30 DAYS
         , CURRENT DATE + 30 DAYS - CURRENT DATE
      FROM SYSIBM.SYSDUMMY1;
    ------------------------------------------------------------------------------
    
    1          2          3         
    ---------- ---------- ----------
    2009-09-07 2009-10-07       100.
    
      1 record(s) selected.

  4. #4
    Join Date
    Jul 2009
    Posts
    150
    Maybe

    select days(CURRENT DATE + 30 DAYS - CURRENT DATE)
    FROM SYSIBM.SYSDUMMY1
    in this case give us the righ result ?

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You can try yourself.

    Anyway, according to manual "DB2 SQL Reference Volume 1":
    DAYS

    DAYS ( expression )

    The schema is SYSIBM.

    The DAYS function returns an integer representation of a date.
    The argument must be a date, timestamp, or a valid character string representation of a date or timestamp that is not a CLOB.
    .....
    The result of "CURRENT DATE + 30 DAYS - CURRENT DATE" is a date duration as I already referenced.
    The result of subtracting one date (DATE2) from another (DATE1) is a date duration that specifies the number of years, months, and days between the two dates. The data type of the result is DECIMAL(8,0).
    So, days(CURRENT DATE + 30 DAYS - CURRENT DATE) would result an error.
    Because, date duration(that is DECIMAL(8,0) ) is not allowed for the argument of DAYS function.
    Last edited by tonkuma; 09-07-09 at 00:31.

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by rdba
    Incorrect:

    select CURRENT DATE + 30 DAYS - CURRENT DATE FROM SYSIBM.SYSDUMMY1

    1
    ----------
    100.



    What am I doing wrong?
    Nothing. The result is exactly what it should be. Maybe RTFM on the functions you are using would help...

    If you want to know the difference in days, then calculate with days and not with dates, for example:
    Code:
    $ db2 "values DAYOFYEAR(CURRENT DATE) + 40 - DAYOFYEAR(CURRENT DATE)"
    
    1
    -----------
             40
    
      1 record(s) selected.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Aug 2009
    Posts
    42
    Marcus's suggestion worked!

Posting Permissions

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