Topic
  • 2 replies
  • Latest Post - ‏2012-04-02T15:34:12Z by stevewatson
SystemAdmin
SystemAdmin
3129 Posts

Pinned topic Calculating how many days are between two dates in DB2?

‏2012-03-02T19:00:38Z |
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
Updated on 2012-04-02T15:34:12Z at 2012-04-02T15:34:12Z by stevewatson
  • B.Hauser
    B.Hauser
    253 Posts

    Re: Calculating how many days are between two dates in DB2?

    ‏2012-03-02T19:14:46Z  
    Hi,

    I think this one is easier (assumed your date is CHAR(8) in the format YYYYMMDD):

    Select Days(Current_Date) - Days(CHDLM concat '000000'),
    Days(Current_Date) - Days(Timestamp_Format(CHDLM, 'YYYYMMDD'))
    From YourTable;

    Birgitta
  • stevewatson
    stevewatson
    5 Posts

    Re: Calculating how many days are between two dates in DB2?

    ‏2012-04-02T15:34:12Z  
    ya the second code sounds to be much easier.it solve with ease.