I want subtract a variable from the current date

DBA > Job Interview Questions > DB2's Dates questions and answers

More DBA job interview questions and answers at http://dba.fyicenter.com/Interview-Questions/

(Continued from previous question...)

I want subtract a variable from the current date. Depending upon certain criteria ...

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 question...)