Filter
Top Products
Query for iSeries date
A date expression is a three-part value (year, month, and day) designating a point in time under the
Gregorian calendar, which is assumed to have been in effect from the year 1 A.D.
1
The year range is
0001 to 9999. The month range is 1 to 12. The day range is 1 to x, where x is 28, 29, 30, or 31,
depending on the month.
A date starts with a digit and has a length of at least 6 characters. Trailing blanks can be included.
Leading zeros can be omitted from the month and day portions. Valid formats allowed for dates are listed
in Table 1. Each format is identified by name and includes an associated abbreviation (used by the CHAR
function) and an example of its use.
Table 1. Formats for Representations of Date Data Types
Format Name Abbreviation Date Format Example
International Standards
Organization
ISO yyyy-mm-dd 1987-10-12
IBM USA Standard USA mm/dd/yyyy 10/12/1987
IBM European Standard EUR dd.mm.yyyy 12.10.1987
Japanese Industrial
Standard Christian era
JIS yyyy-mm-dd 1987-10-12
OS/400 format DMY
YMD
MDY
JUL
DD/MM/YY
YY/MM/DD
MM/DD/YY
YYDDD
YYYYDDD
12/10/87
87/12/10
12/10/87
87/344
1987/344
The date separator can be either
a period (.), slash (/), comma (,),
dash (-), or blank space for the
OS/400 date format data types.
Note about using SAA date format: To prevent confusion about the date value, use the Systems
Application Architecture
®
(SAA) date formats (ISO, USA, EUR, or
JIS) when specifying date constants in a multilingual environment or
when a query is to be displayed or changed by different people.
If the OS/400 two-digit year format is used, the range of dates is from 1940 through 2039. Any year from
40 through 99 is assumed to have a century of 19. Any year from 00 through 39 is assumed to have a
century of 20. If a value outside of that range is in a field with a two-digit year format, it will be shown on a
report as +’s. Use the CHAR function on that field specifying a SAA date format, and then select the result
field for the report.
Query for iSeries time
A time expression is a three-part value (hour, minute, and second) that designates the time of day using a
24-hour clock. The hour range is 0 to 24, while the minute and second range is 0 to 59. If the hour is 24,
the minute and second specifications are both zero.
A time data type starts with a digit and has a length of at least 4 characters. Trailing blanks can be
included; a leading zero can be omitted from the hour part of the time, and seconds can be omitted
entirely. If you choose to omit seconds, 0 seconds is assumed. Thus, 13.30 is equivalent to 13.30.00.
1. Historical dates do not always follow the Gregorian calendar. Dates between 1582-10-04 and 1582-10-15 are accepted as valid
dates although they never existed in the Gregorian calendar.
Chapter 5. Defining result fields in Query for iSeries
67