DB2 Universal Database for iSeries SQL Reference

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 UTF-16 or UCS-2 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 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 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 book.)

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 book.)

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) book.)

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 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 ODBC, 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 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'

In the USA time format, the hour must not be greater than 12 and cannot be 0 except for the special case of 00:00 AM. In the USA format, a single space character exists between the minutes portion of the time of day and the AM or PM. Using 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 11: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 book.)

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 book.)

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) book.)

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 ODBC, see the iSeries Access Family 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 .)

Timestamp strings

A string representation of a timestamp is a character 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'
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, and hour 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.


Footnotes:

21
This is an earlier version of the ISO format. JIS can be used to get the current ISO format.


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]