Calculating how many days are 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 seems to be working. So basically what i need to get is something like this.

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

I know that if I remove CHDLM and specify a date like '2012-02-20' it works but I need to be able to run this against that field in the table. I also try this query that was given to me by a friend by is not working either.

 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 will be greatly appreciate it. Thanks

Answer 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 (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