Filter
Top Products
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 +’sonthe
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