I have a funny situation with using the DAYS() function:

1.Select DAYS(CURRENT DATE) - DAYS(FIRSTDAY) from TABLE

gives incorrect output (far too high)

2.Select RESUMEDDAY - FIRSTDAY from TABLE

(both datefields) gives proper results.

3.Select CURRENT DATE - FIRSTDAY from TABLE

returns ridiculous high integers, even much higher than 1.

4. Select TIMESTAMPDIFF(16,CHAR(CURRENT DATE - FIRSTDAY)) from TABLE

returns about the same output as 1. , but slightly lower

What am i missing to work with the system date ?

I think your problem is how date, time, and timestamp arithmatic is done.

Using your examples:

1) will return the number of days between the two dates as an integer

2) and 3) return a duration (decimal) that is a representation of years, months and days between dates (YYYYMMDD.)

4) TIMESTAMPDIFF returns an approximation

Examples I just ran:

DB:LCAD->select days(current date) - days('08/01/2004') from sysibm.sysdummy1

1
-----------
17

1 record(s) selected.

DB:LCAD->select current date - '08/01/2004' from sysibm.sysdummy1

1
----------
17.

1 record(s) selected.

DB:LCAD->select days(current date) - days('08/01/1994') from sysibm.sysdummy1

1
-----------
3670

1 record(s) selected.

DB:LCAD->select current date - '08/01/1994' from sysibm.sysdummy1

1
----------
100017.

1 record(s) selected.

DB:LCAD->select timestampdiff(16,char(current date -'08/01/2004')) from sysibm.sysdummy1

1
-----------
17

1 record(s) selected.

Read the SQL Reference manual on date, time, and timestamp arithmatic.

HTH

Andy

Sorry to bring up the subject. I was missing the fact that a lot of the data was from year 2003

Maybe if you would post the results you are getting. I may be able to help you more.

Andy

No. I mean the issue is resolved with the high figures. I was expecting to compare 2004 dates with current date, but a lot of the data was from 2003. And hence the high values.....