Appendix C: DDS Date and Time Format Considerations

The OS/400 database, known as DB2 for OS/400 or the universal database, supports many types of field attributes, including integrated DATE, TIME and TIMESTAMP data types. To declare a field as being of type DATE, the L data type is specified. To declare a field as being of type TIME, the T data type is specified. To declare a field as being of type TIMESTAMP, the Z data type is specified. The data type for a field is specified in column 35 of the OS/400 data description specification. The DDS ruler used to identify the layout of a database field specification follows:

 1...v....1....v....2....v....3....v....4....v....5....v....6....v....7....v....8 .....A..........R.Fieldname+RLen++TDc.......Functions+++++++++++++++++++++++++++ 

DATABASE DATE AND TIME OVERVIEW

Date and time data types support several formats. These formats control how the date or time value is presented at a high-level. Internally, all DATE fields are stored as a 4-byte value. All TIME fields are stored as a 3-byte value. The TIMESTAMP data type supports one format and is stored internally as a 10-byte value. For example, the date July 23, 1958, is stored as 714982 in the internal 4-byte format. See Figure C.1. This format is often referred to as Super Julian. Super Julian is the number of days since January 1, 0001. The date 0001-01-01 (in ISO format) is the base date used by RPG IV.

0	7	4	8
0	1	9	2

Figure C.1: Internal storage format for date values.

The internal date, time, and timestamp formats cannot be accessed through conventional programming. It is documented here for completeness.

Because the external date and time formats don't affect the internal date format, the external format is not a consideration when designing access paths or selecting key fields for a file. Also, because it is the default value used throughout the RPG IV and DDS languages, the *ISO date format offers the fewest compatibility problems.

The standard for specifying the length of a date, time, or timestamp field in RPG is to not specify the length. The format of the field determines its length. The compiler generates a length entry, automatically, as follows:

• DATE fields allocate 6, 8, or 10 bytes in a database file.

• TIME fields allocate 8 bytes in a database file.

• TIMESTAMP fields allocate 26 bytes in a database file.

For more information on the attributes of DATE, TIME, and TIMESTAMP fields, see Tables C.1 and C.2

Table C.1: Date Formatting Codes

Date Format	Description	Output Format	Separator	Length	Sample
*JOB	job attribute	compile-time
*MDY	Month, Day, Year	mm/dd/yy	/ - . , &	8	02/17/95
*DMY	Day, Month, Year	dd/mm/yy	/ - . , &	8	17/02/95
*YMD	Year, Month, Day	yy/mm/dd	/ - . , &	8	95/02/17
*JUL	Julian (year, day of year 1 - 365)	yy/ddd	/ - . , &	6	95/048
*ISO	International Standards Organization	ccyy-mm-dd	- (fixed)	10	1995-02-17
*USA	IBM's USA Standard	mm/dd/ccyy	/ (fixed)	10	02/17/1995
*EUR	IBM's European Standard	dd.mm.ccyy	(fixed)	10	17.02.1995
*JIS	Japanese Industrial Standard	ccyy-mm-dd	- (fixed)	10	1995-02-17

Table C.2: Time Formatting Codes

Time Format	Description	Output Format	Separator	Length	Sample
*JOB	Job attribute	compile-time
*HMS	Hours, Minutes, Seconds	hh:mm:ss	: . , &	8	15:30:00
*ISO	International Standards Organization	hh.mm.ss	(fixed)	8	15:30:00
*USA	IBM's USA Standard	hh:mm am hh:mm pm	: (fixed)	8	3:30 PM
*EUR	IBM's European Standard	hh.mm.ss	(fixed)	8	15:30:00
*JIS	Japanese Industrial Standard	hh:mm:ss	: (fixed)	8	15:30:00