I have a table with a column Frequency which is deimal of two digits, indicates the number of months in decimal (36 Months). i have to build a query in such a way that i have to deduct from the system date.
Select X,Y,Z from Table where End date < (System date - Frequency)
i have to convert 36 months to a date from the system date.
FROM FPOIS912.VW450_LAR_OVK AS VW450,
FPOIS912.VW455_LAR_EXP_FREQ AS VW455
WHERE VW450.LAR_ID BETWEEN 'LAR1000001' AND 'LAR1000031'
AND VW450.EIND_DT < (CURRENT DATE - VW455.PURGE_FREQ)
Just a cautionary note about using date durations:
When adding durations to dates, adding one month to a given date
gives the same date one month later unless that date does not exist in the
later month. In that case, the date is set to that of the last day of the later
month. For example, January 28 plus one month gives February 28; and one
month added to January 29, 30, or 31 results in either February 28 or, for a
leap year, February 29.
If one or more months is added to a given date and then the same number of
months is subtracted from the result, the final date is not necessarily the
same as the original date.