Calculating the number of days between two dates in DB2?

Question

I need to get the difference in days between two dates in DB2. I tried a couple of different queries, but nothing works. So basically what I need is something like this.

SELECT DAYS (CURRENT DATE) - DAYS (DATE(CHDLM)) FROM CHCART00 WHERE CHSTAT = '05'; 

I know that if I delete CHDLM and set a date like "2012-02-20", it works, but I need to be able to run this against this field in the table. I am also trying to fulfill this request, which was provided to me by a friend, also does not work.

  select days (current date) - 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'; 

Please any help would be greatly appreciated. Thanks

Answer 1

I think @Siva is on the right track (using DAYS() ), but the nested CONCAT() makes me dizzy. Here is my trick.
Oh, it makes no sense to refer to sysdummy1 , since you need to pull it out of the table independently.
Also, do not use the implicit join syntax - it is considered an SQL anti-pattern.

I converted the date conversion to CTE for readability here, but nothing prevents you from doing this inline.

 WITH Converted (convertedDate) as (SELECT DATE(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 

Answer 2

I ran into the same issue in the embedded DB2 DB2 Derby database in a Java desktop application, and after a day of searching, I finally found how to do it:

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

check this site for more information

Answer 3

 values timestampdiff (16, char( timestamp(current timestamp + 1 year + 2 month - 3 day)- timestamp(current timestamp))) 1 = 422 values 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(CURRENT TIMESTAMP - TIMESTAMP_FORMAT(CHDLM, 'YYYYMMDD')) FROM CHCART00 WHERE CHSTAT = '05' 

EDIT

As noted by X-Zero, this function returns only an estimate. It's true. For accurate results, I would use the following to get the difference in days between the two dates a and b:

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

Answer 4

It seems that one closing bracket is missing in ,right(a2.chdlm,2)))) from sysibm.sysdummy1 a1,

So your request will be

 select days(current date) - 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'; 

Answer 5

Is not it:

 SELECT CURRENT_DATE - CHDLM FROM CHCART00 WHERE CHSTAT = '05'; 

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

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