IBM SC41-5210-04 manual Query for iSeries date, Query for iSeries time

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	ISO	yyyy-mm-dd	1987-10-12
Organization
IBM USA Standard	USA	mm/dd/yyyy	10/12/1987
IBM European Standard	EUR	dd.mm.yyyy	12.10.1987
Japanese Industrial	JIS	yyyy-mm-dd	1987-10-12
Standard Christian era
OS/400 format	DMY	DD/MM/YY	12/10/87
	YMD	YY/MM/DD	87/12/10
	MDY	MM/DD/YY	12/10/87
	JUL	YYDDD	87/344
		YYYYDDD	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