Calculating How Many Days Are Between Two Dates In Db2?

I need to get the difference in days between two dates in DB2. I tried a couple of different queries but nothing seems to be working. So basically what i need to get is something l

Solution 1:

I think that @Siva is on the right track (using DAYS()), but the nested CONCAT()s are making me dizzy. Here's my take. Oh, there's no point in referencing sysdummy1, as you need to pull from a table regardless. Also, don't use the implicit join syntax - it's considered an SQL Anti-pattern.

I'be wrapped the date conversion in a CTE for readability here, but there's nothing preventing you from doing it inline.

WITH Converted (convertedDate) as (SELECTDATE(SUBSTR(chdlm, 1, 4) ||'-'||
                                               SUBSTR(chdlm, 5, 2) ||'-'||    
                                               SUBSTR(chdlm, 7, 2))
                                   FROM Chcart00
                                   WHERE chstat ='05')

SELECT DAYS(CURRENT_DATE) - DAYS(convertedDate)
FROM Converted

Solution 2:

I faced the same problem in Derby IBM DB2 embedded database in a java desktop application, and after a day of searching I finally found how it's done :

SELECT days (table1.datecolomn) - days (currentdate) FROM table1 WHERE days (table1.datecolomn) - days (currentdate) >5

for more information check this site

Solution 3:

It seems like one closing brace is missing at ,right(a2.chdlm,2)))) from sysibm.sysdummy1 a1,

So your Query will be

select days(currentdate) - days(date(select concat(concat(concat(concat(left(a2.chdlm,4),'-'),substr(a2.chdlm,4,2)),'-'),right(a2.chdlm,2)))) from sysibm.sysdummy1 a1, chcart00 a2 where chstat ='05';

Solution 4:

Wouldn't it just be:

SELECTCURRENT_DATE- CHDLM FROM CHCART00 WHERE CHSTAT ='05';

That should return the number of days between the two dates, if I understand how date arithmetic works in DB2 correctly.

If CHDLM isn't a date you'll have to convert it to one. According to IBM the DATE() function would not be sufficient for the yyyymmdd format, but it would work if you can format like this: yyyy-mm-dd.

Solution 5:

values timestampdiff (16, char( 
    timestamp(currenttimestamp+1year+2month-3day)-timestamp(currenttimestamp)))
1=422values timestampdiff (16, char( 
    timestamp('2012-03-08-00.00.00')-timestamp('2011-12-08-00.00.00')))
1=90

---------- EDIT BY galador

SELECT TIMESTAMPDIFF(16, CHAR(CURRENTTIMESTAMP- TIMESTAMP_FORMAT(CHDLM, 'YYYYMMDD'))
FROM CHCART00
WHERE CHSTAT ='05'

EDIT

As it has been pointed out by X-Zero, this function returns only an estimate. This is true. For accurate results I would use the following to get the difference in days between two dates a and b:

SELECT days (currentdate) - days (date(TIMESTAMP_FORMAT(CHDLM, 'YYYYMMDD')))
FROM CHCART00 
WHERE CHSTAT ='05';