(Continued from previous part...)

Q: Is there a way in DB2 date arithmetic to express the duration resulting from date subtraction, as a total-number-of-days (exact total, and not an approximate total)? To illustrate, the query:

     SELECT DATE ('03/01/2004') - '12/01/2003'

returns a duration of 00000300 (i.e. 3-months). And those 3-months encompass a 29-day February plus a 31-day January plus a 31-day December (total 91 days). So I would be looking for a query which would return the number 91. Any ideas?

A: The answer lies in using the DAYS function. The following should return what you need:

     SELECT DAYS('03/01/2004') - DAYS('12/01/2003')

This query will return to you the exact number of days between the two dates.

Q: I want subtract a variable from the current date. Depending upon certain criteria, I want to subtract a number of days from the date, but I am getting an error: undefined or unusable variable. My code is:

     SELECT A.DOC, A.TRAN_DATE, A.CRTE_DATE..
     FROM ABC.TODDOC A,.....
     WHERE  A.CMPY = B.CMPY AND ....
     AND (A.CRTE_DATE <= CURRENT DATE - :SUB2 DAYS) AND 
              (A.CRTE_DATE > CURRENT DATE - :SUB3 DAYS)   AND  .....

The SUB2 and SUB3 host variables are day-of-week fields. Please, help… what is the correct syntax or alternative solution?

A: You cannot use a labeled duration with a host variable like that. The solution is to use a date duration. A date duration represents a number of years, months, and days expressed as a DECIMAL(8,0) number. To be properly interpreted, the number must have the format yyyymmdd, where yyyy represents the number of years, mm the number of months, and DD the number of days. So, your query would become:

     SELECT A.DOC, A.TRAN_DATE, A.CRTE_DATE..
     FROM ABC.TODDOC A,.....
     WHERE  A.CMPY = B.CMPY AND ....
     AND (A.CRTE_DATE <= CURRENT DATE - :SUB2) AND 
              (A.CRTE_DATE > CURRENT DATE - :SUB3)   AND  .....

And :SUB2 and :SUB3 become DECIMAL(8,0) date durations. For example, to specify 3 DAYS as the date duration, you would use the value 00000003. This specifies 0 years, 0 months, and 3 days.

(Continued on next part...)