Datetime values

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.

Date

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.

Time

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.

Timestamp

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.

Datetime variables

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.

String representations of datetime values

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.

Date strings

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:

Table 5. Formats for String Representations of Dates
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:

Table 6. Default Date Format 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.

(For more information about CRTSQLxxx commands, see the Embedded SQL programming topic.)
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.

(For more information about STRSQL and RUNSQLSTM commands, see the SQL programming topic.)
Call Level Interface (CLI) on the server SQL_ATTR_DATE_FMT and SQL_ATTR_DATE_SEP environment or connection variables

(For more information about CLI, see the SQL Call Level Interfaces (ODBC) topic.)
JDBC or SQLJ on the server using IBM Developer Kit for Java Date Format and Date Separator connection property

(For more information about JDBC and SQLJ, see the IBM Developer Kit for Java topic in the iSeries Information Center.)
ODBC on a client using the iSeries Access Family ODBC Driver Date Format and Date Separator in the Advanced Server Options in ODBC Setup

(For more information about ODBC, see the iSeries(TM) Access category in the iSeries Information Center.)
JDBC on a client using the IBM Toolbox for Java Format in JDBC Setup

(For more information about JDBC, see the iSeries Access category in the iSeries Information Center.)

(For more information about the IBM Toolbox for Java, see IBM Toolbox for Java topic in the iSeries Information Center .)
Time strings

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:

Table 7. Formats for String Representations of Times
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:

Table 8. USA Time Format
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:

Table 9. Default Time Format 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.

(For more information about CRTSQLxxx commands, see the Embedded SQL programming topic.)
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.

(For more information about STRSQL and RUNSQLSTM commands, see the SQL programming topic.)
Call Level Interface (CLI) on the server SQL_ATTR_TIME_FMT and SQL_ATTR_TIME_SEP environment or connection variables

(For more information about CLI, see the SQL Call Level Interfaces (ODBC) topic.)
JDBC or SQLJ on the server using IBM Developer Kit for Java Time Format and Time Separator connection property object

(For more information about JDBC and SQLJ, see the IBM Developer Kit for Java topic in the iSeries Information Center.)
ODBC on a client using the iSeries Access Family ODBC Driver Time Format and Time Separator in the Advanced Server Options in ODBC Setup

(For more information about ODBC, see the iSeries Access Family category in the iSeries Information Center.)
JDBC on a client using the IBM Toolbox for Java Format in JDBC Setup

(For more information about the IBM Toolbox for Java, see IBM Toolbox for Java topic in the iSeries Information Center .)
Timestamp strings

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:

Table 10. Formats for String Representations of Timestamps
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.


20.
Note that 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.
21.
This is an earlier version of the ISO format. JIS can be used to get the current ISO format.