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 1. 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 in one of the following formats:

Table 1. Formats for String Representations of Times
Format Name Abbreviation Time Format Example
International Standards Organization (*ISO) ISO 'hh.mm.ss' 1 '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 2. 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 3. 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 collection.)
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 collection.)
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 collection.)
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 collection.)
ODBC on a client using the IBM i Access Family ODBC Driver Time Format and Time Separator in the Advanced Server Options in ODBC Setup

(For more information about ODBC, see the IBM i Access Family topic collection.)
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 collection.)
1 This is an earlier version of the ISO format. JIS can be used to get the current ISO format.