Problem solve Get help with specific problems with your technologies, process and projects.

Date manipulation in SQL

Work with date manipulation within SQL.

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),5,2)|| '-'||

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:


More on this topic

The Best Web Links: tips, tutorials and more.

Ask your programming questions--or help out your peers by answering them--in our live discussion forums.

Ask the Experts yourself: Our application development gurus are waiting to answer your programming questions.



Dig Deeper on iSeries CL programming