14

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

jorame
  • 2,147
  • 12
  • 41
  • 58

5 Answers5

17

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
Clockwork-Muse
  • 12,806
  • 6
  • 31
  • 45
  • Sorry, one more question, what if i need to select more fields from the CHCART00 table, how can I do that? – jorame Mar 06 '12 at 21:50
4

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 (current date) FROM table1 WHERE days (table1.datecolomn) - days (current date) > 5

for more information check this site

E_X
  • 3,722
  • 1
  • 14
  • 15
  • 1
    this would only work if the dates are known to always be in the same month. (`day` just returns the day-of-month field) Using `days` should work for the general cases, as it returns the number of days since the epoch. – Brad Mace Dec 29 '15 at 20:32
0
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 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 (current date) - days (date(TIMESTAMP_FORMAT(CHDLM, 'YYYYMMDD')))
FROM CHCART00 
WHERE CHSTAT = '05';
Isaac
  • 2,701
  • 4
  • 30
  • 47
  • How exactly can I implement this on my query? – jorame Mar 02 '12 at 19:09
  • I added in a query that I think should get you what you're wanting (assuming `CHDLM` is a date or timestamp type, or a properly formatted string). – bhamby Mar 02 '12 at 19:31
  • CHDLM is formatted like yyyymmdd, do you think this query will work with that? – jorame Mar 02 '12 at 19:37
  • One more thing, the DB is DB2 – jorame Mar 02 '12 at 19:38
  • You can use the [`TIMESTAMP_FORMAT()`](http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0007107.html) function to convert it from a string to a `TIMESTAMP`. I updated my part to use this function. – bhamby Mar 02 '12 at 23:13
  • 1
    Unfortuanately, this function returns an **estimate** only. Among other things, it assumes that there are only 30 days in a month... – Clockwork-Muse Mar 02 '12 at 23:17
0

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

So your Query 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';
Siva Charan
  • 17,940
  • 9
  • 60
  • 95
0

Wouldn't it just be:

SELECT CURRENT_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.

David Faber
  • 12,277
  • 2
  • 29
  • 40
  • Unfortuanately, the results of these operations is an _indefinite period_ (ie. 2 months, 3 days), even when the starting format is valid. The OP specifically asked for the number of days. – Clockwork-Muse Mar 02 '12 at 23:34
  • Ah, my apologies, it should be DAYS(CURRENT_DATE) - DAYS(CHDLM) – David Faber Mar 03 '12 at 03:41