Date manipulation within SQL has been simpler ever since IBM introduced DATE and DAYS functions in SQL/400.
I have had situations were I had to subtract a specific number of days from a given date and also display the result date, or determine the number of days between two dates and display the resulting days, etc.
The DATE and DAYS function in SQL provides a powerful solution for these situations. It doesn't matter whether the fields for calculation are numeric/alphanumeric or date type.
Here is an example SQL that will explain the DATE functions:
SELECT Item, Date(Days(SUBSTR(CHAR(MFDATE),1,4)||'-'||
SUBSTR(CHAR(MFDATE),7,2)) + EXPIRE)
In the above example, ITEMMASTER is a database file that has all the item information. Basically, the above SQL will return a list of items and their expiry date from the file ITEMMASTER. MFDATE is a numeric field with the manufactured date in YYYYMMDD format and EXPIRE is an alphanumeric field with the product life in days. Our default system date separator is '-'.
Hence any input date format to the function DAYS should be YYYY-MM-DD and the function will return the integer representation of the date. The Date function then adds the value in EXPIRE to the integer date and will then convert that back to the normal date format.
The following are the date functions available in SQL: