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® 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 1.
The database manager recognizes the string as a date when it is in one of the following formats:
- In the format specified by the default date format
- In one of the IBM SQL standard date formats
- In the unformatted Julian format
| Format Name | Abbreviation | Date Format | Example |
|---|---|---|---|
| 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. (For more information about CRTSQLxxx commands, see the Embedded SQL programming topic collection.) |
| 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 SQL programming.) |
| 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 SQL Call Level Interfaces (ODBC).) |
| 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 IBM Developer Kit for Java.) |
| ODBC on a client using the IBM i Access Family ODBC Driver | Date Format and Date Separator in the Advanced Server Options in ODBC Setup (For more information about ODBC, see System i® Access.) |
| JDBC on a client using the IBM Toolbox for Java | Format in JDBC Setup (For more information about JDBC, see System i Access.) (For more information about the IBM Toolbox for Java, see IBM Toolbox for Java.) |