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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] Calculating how many days are between two dates in DB2?

Examples related to sql

Examples related to database

Examples related to db2