Although datetime values can be used in certain arithmetic and string operations and are compatible with certain strings, they are neither strings nor numbers. However, strings can represent datetime values; see String representations of datetime values.
A date is a three-part value (year, month, and day) designating a point in time under the Gregorian calendar20, which is assumed to have been in effect from the year 1 A.D. The range of the year part is 0001 to 9999. The date formats *JUL, *MDY, *DMY, and *YMD can only represent dates in the range 1940 through 2039. The range of the month part is 1 to 12. The range of the day part is 1 to x, where x is 28, 29, 30, or 31, depending on the month and year.
The internal representation of a date is a string of 4 bytes that contains an integer. The integer (called the Scaliger number) represents the date.
The length of a DATE column as described in the SQLDA is 6, 8, or 10 bytes, depending on which format is used. These are the appropriate lengths for string representations for the value.
A time is a three-part value (hour, minute, and second) designating a time of day using a 24-hour clock. The range of the hour part is 0 to 24, while the range of the minute and second parts is 0 to 59. If the hour is 24, the minute and second specifications are both zero.
The internal representation of a time is a string of 3 bytes. Each byte consists of two packed decimal digits. The first byte represents the hour, the second byte the minute, and the last byte the second.
The length of a TIME column as described in the SQLDA is 8 bytes, which is the appropriate length for a string representation of the value.
A timestamp is a seven-part value (year, month, day, hour, minute, second, and microsecond) that designates a date and time as defined previously, except that the time includes a fractional specification of microseconds.
The internal representation of a timestamp is a string of 10 bytes. The first 4 bytes represent the date, the next 3 bytes the time, and the last 3 bytes the microseconds (the last 3 bytes contain 6 packed digits).
The length of a TIMESTAMP column as described in the SQLDA is 26 bytes, which is the appropriate length for the string representation of the value.
Character string variables are normally used to contain date, time, and timestamp values. However, date, time, and timestamp variables can also be specified in ILE COBOL and ILE RPG. Date, time, and timestamp variables can also be specified in Java(TM) as java.sql.Date, java.sql.Time, and java.sql.Timestamp respectively.
Values whose data types are DATE, TIME, or TIMESTAMP are represented in an internal form that is transparent to the user of SQL. Dates, times, and timestamps, however, can also be represented by character or Unicode graphic strings. Only ILE RPG and ILE COBOL support datetime variables. To be retrieved, a datetime value can be assigned to a string variable. The format of the resulting string will depend on the default date format and the default time format in effect when the statement was prepared. The default date and time formats are set based on the date format (DATFMT), the date separator (DATSEP), the time format (TIMFMT), and the time separator (TIMSEP) parameters.
When a valid string representation of a datetime value is used in an operation with an internal datetime value, the string representation is converted to the internal form of the date, time, or timestamp before the operation is performed. If the CCSID of the string represents a foreign encoding scheme (for example, ASCII), it is first converted to the coded character set identified by the default CCSID before the string is converted to the internal form of the datetime value.
The following sections define the valid string representations of datetime values.
A string representation of a date is a character or a Unicode graphic string that 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 when using the IBM(R) SQL standard formats. Each IBM SQL standard format is identified by name and includes an associated abbreviation (for use by the CHAR function). Other formats do not have an abbreviation to be used by the CHAR function. The separators for two-digit year formats are controlled by the date separator (DATSEP) parameter. Valid string formats for dates are listed in Table 5.
The database manager recognizes the string as a date when it is either:
Format Name | Abbreviation | Date Format | Example |
---|---|---|---|
ANSI/ISO SQL standard date format (-) | - | DATE 'yyyy-mm-dd' | DATE '1987-10-12' |
International Standards Organization (*ISO) | ISO | 'yyyy-mm-dd' | '1987-10-12' |
IBM USA standard (*USA) | USA | 'mm/dd/yyyy' | '10/12/1987' |
IBM European standard (*EUR) | EUR | 'dd.mm.yyyy' | '12.10.1987' |
Japanese industrial standard Christian era (*JIS) | JIS | 'yyyy-mm-dd' | '1987-10-12' |
Unformatted Julian | - | 'yyyyddd' | '1987285' |
Julian (*JUL) | - | 'yy/ddd' | '87/285' |
Month, day, year (*MDY) | - | 'mm/dd/yy' | '10/12/87' |
Day, month, year (*DMY) | - | 'dd/mm/yy' | '12/10/87' |
Year, month, day (*YMD) | - | 'yy/mm/dd' | '87/12/10' |
The default date format can be specified through the following interfaces:
SQL Interface | Specification |
---|---|
Embedded SQL | The DATFMT and DATSEP parameters are specified
on the Create SQL Program (CRTSQLxxx) commands. The SET OPTION statement can
also be used to specify the DATFMT and DATSEP parameters within the source
of a program containing embedded SQL.
|
Interactive SQL and Run SQL Statements | The DATFMT and DATSEP parameters on the Start
SQL (STRSQL) command or by changing the session attributes. The DATFMT and
DATSEP parameters on the Run SQL Statements (RUNSQLSTM) command.
|
Call Level Interface (CLI) on the server | SQL_ATTR_DATE_FMT and SQL_ATTR_DATE_SEP environment
or connection variables
|
JDBC or SQLJ on the server using IBM Developer Kit for Java | Date Format and Date Separator connection
property
|
ODBC on a client using the iSeries Access Family ODBC Driver | Date Format and Date Separator in the Advanced
Server Options in ODBC Setup
|
JDBC on a client using the IBM Toolbox for Java | Format in JDBC Setup
|
A string representation of a time is a character or a Unicode graphic string that 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, an implicit specification of 0 seconds is assumed. Thus, 13.30 is equivalent to 13.30.00.
Valid string formats for times are listed in Table 7. Each IBM SQL standard format is identified by name and includes an associated abbreviation (for use by the CHAR function). The other format (*HMS) does not have an abbreviation to be used by the CHAR function. The separator for the *HMS format is controlled by the time separator (TIMSEP) parameter.
The database manager recognizes the string as a time when it is either:
Format Name | Abbreviation | Time Format | Example |
---|---|---|---|
ANSI/ISO SQL standard time format (-) | - | TIME 'hh:mm:ss' | TIME '13:30:05' |
International Standards Organization (*ISO) | ISO | 'hh.mm.ss' 21 | '13.30.05' |
IBM USA standard (*USA) | USA | 'hh:mm AM' (or PM) | '1:30 PM' |
IBM European standard (*EUR) | EUR | 'hh.mm.ss' | '13.30.05' |
Japanese industrial standard Christian era (*JIS) | JIS | 'hh:mm:ss' | '13:30:05' |
Hours, minutes, seconds (*HMS) | - | 'hh:mm:ss' | '13:30:05' |
The following additional rules apply to the USA time format:
In the USA format, using the ISO format of the 24-hour clock, the correspondence between the USA format and the 24-hour clock is as follows:
USA Format | 24-Hour Clock |
---|---|
12:01 AM through 12:59 AM | 00.01.00 through 00.59.00 |
01:00 AM through 11:59 AM | 01:00.00 through 11:59.00 |
12:00 PM (noon) through 11:59 PM | 12:00.00 through 23.59.00 |
12:00 AM (midnight) | 24.00.00 |
00:00 AM (midnight) | 00.00.00 |
The default time format can be specified through the following interfaces:
SQL Interface | Specification |
---|---|
Embedded SQL | The TIMFMT and TIMSEP parameters are specified
on the Create SQL Program (CRTSQLxxx) commands. The SET OPTION statement can
also be used to specify the TIMFMT and TIMSEP parameters within the source
of a program containing embedded SQL.
|
Interactive SQL and Run SQL Statements | The TIMFMT and TIMSEP parameters on the Start
SQL (STRSQL) command or by changing the session attributes. The TIMFMT and
TIMSEP parameters on the Run SQL Statements (RUNSQLSTM) command.
|
Call Level Interface (CLI) on the server | SQL_ATTR_TIME_FMT and SQL_ATTR_TIME_SEP environment
or connection variables
|
JDBC or SQLJ on the server using IBM Developer Kit for Java | Time Format and Time Separator connection
property object
|
ODBC on a client using the iSeries Access Family ODBC Driver | Time Format and Time Separator in the Advanced
Server Options in ODBC Setup
|
JDBC on a client using the IBM Toolbox for Java | Format in JDBC Setup
|
A string representation of a timestamp is a character or a Unicode graphic string that starts with a digit and has a length of at least 16 characters. The complete string representation of a timestamp has one of the following forms:
Format Name | Time Format | Example |
---|---|---|
ANSI/ISO SQL standard | TIMESTAMP 'yyyy-mm-dd hh:mm:ss.nnnnnn' | TIMESTAMP '1990-03-02 08:30:00.010000' |
ISO timestamp | 'yyyy-mm-dd hh:mm:ss.nnnnnn' | '1990-03-02 08:30:00.010000' |
IBM SQL | 'yyyy-mm-dd-hh.mm.ss.nnnnnn' | '1990-03-02-08.30.00.010000' |
14-character form | 'yyyymmddhhmmss' | '19900302083000' |
Trailing blanks can be included. Leading zeros can be omitted from the month, day, hour, and second part of the timestamp when using the timestamp form with separators. Trailing zeros can be truncated or omitted entirely from microseconds. If you choose to omit any digit of the microseconds portion, an implicit specification of 0 is assumed. Thus, 1990-3-2-8.30.00.10 is equivalent to 1990-03-02-08.30.00.100000.
A timestamp whose time part is 24.00.00.000000 is also accepted.
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.