IBM SC41-5210-04 Incrementing and decrementing dates in Query for iSeries, SUBSTRCHARDATE,5,2’/’

then MONTH(RESULT) = 12 + MONTH(DATE1) - MONTH(DATE2). YEAR(DATE2) is then incremented by 1.

YEAR(RESULT) = YEAR(DATE1) - YEAR(DATE2).

For example, the result of DATE(’3/15/2000’) - ’12/31/1999’ is 215, or a duration of 0 years, 2 months, and 15 days.

Incrementing and decrementing dates in Query for iSeries

The result of adding a duration to or subtracting a duration from a date is itself a date. It must be between January 1, 0001 and December 31, 9999 inclusive. If a duration of years is added or subtracted, only the year portion of the date is affected.

If a duration of months is added or subtracted, only the months and years are affected. The day portion is unchanged unless the result is not valid (September 31, for example).

Adding or subtracting a duration of days affects the day portion and possibly the month and year.

Date durations, either positive or negative, may be added to and subtracted from dates. The result is a date that has been incremented or decremented by a specified number of years, months, and days.

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. Then, the date is set to the last day of the later month. For example, January 28 plus one month gives you February 28. But January 29, 30, or 31 plus one month results in February 28 or in a leap year, February 29.

Note: If one or more months is added to a given date and the same number of months is subtracted from the result, the final date is not necessarily the same as the original date.

Converting a numeric field to a date field in Query for iSeries

The following is a way to convert a numeric field that contains a date to a date data-type field.

In this example, the job date format is YMD and the job date separator is /. A file contains a numeric date field call NUMDATE, length 6, which contains dates in the format MDY (month day year). The first value in NUMDATE is 011392.

Create the following result fields:

CHARDATE	DIGITS(NUMDATE)
CHARDAT2	SUBSTR(CHARDATE,5,2)’/’
	SUBSTR(CHARDATE,1,2)’/’
	SUBSTR(CHARDATE,3,2)
NEWDATE	DATE(CHARDAT2)

NEWDATE contains the internal representation of year 1992 month 01 day 13. If the date is shown on the report, it shows as 92/01/13.

Note: If the date value resulting is outside the range of 1940 through 2039, it will show as +’s on the report. Use the CHAR function to see the correct value.

Working with numeric dates in Query for iSeries

If you are using numeric fields to represent dates, you can use arithmetic operations to manipulate the dates without using the Query date functions. Example 1 shows a method of converting a numeric field containing a date from MMDDYY format to YYMMDD format. Example 2 shows the reverse conversion: YYMMDD format to MMDDYY format.

Example 1: Working with numeric dates in Query for iSeries: The following query defines a MMDDYY numeric field conversion to a YYMMDD numeric field, which is more suitable for sorting and for

Chapter 5. Defining result fields in Query for iSeries 71