incorrect results with DAYS

rdba · #1 (**permalink**) 09-06-09, 10:07

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?

Marcus_A · #2 (**permalink**) 09-06-09, 11:09

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

tonkuma · #3 (**permalink**) 09-06-09, 11:34

Quote:

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.

DB2Plus · #4 (**permalink**) 09-06-09, 20:01

Maybe

Quote:

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

in this case give us the righ result ?

tonkuma · #5 (**permalink**) 09-06-09, 23:25

You can try yourself.

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

Quote:

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.

Quote:

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.

stolze · #6 (**permalink**) 09-07-09, 05:59

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.

rdba · #7 (**permalink**) 09-07-09, 18:35

Marcus's suggestion worked!

Internet Services

Web Development

Digital Marketing

Consumer Tech