Values whose data types are DATE, TIME, or TIMESTAMP are represented in an internal form that is transparent to the SQL user. Dates, times, and timestamps can, however, also be represented by character strings, and these representations directly concern the SQL user since there are no constants or variables whose data types are DATE, TIME, or TIMESTAMP. Thus, to be retrieved, a datetime value must be assigned to a character string variable. Note that the CHAR function can be used to change a datetime value to a string representation. The character string representation is normally the default format of datetime values associated with the country code of the database, unless overridden by specification of the DATETIME option when the program is precompiled or bound to the database.
No matter what its length, a large object string or LONG VARCHAR cannot be used as the string that represents a datetime value; otherwise an error is raised (SQLSTATE 42884).
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. The following sections define the valid string representations of datetime values.
A string representation of a date is a character string that starts with a digit and has a length of at least 8 characters. Trailing blanks may be included; leading zeros may be omitted from the month and day portions.
Valid string formats for dates are listed in Table 1. Each format is
identified by name and includes an associated abbreviation and an example of
its use.
Table 3. Formats for String Representations of Dates
| Format Name | Abbreviation | Date Format | Example |
|---|---|---|---|
| International Standards Organization | ISO | yyyy-mm-dd | 1991-10-27 |
| IBM USA standard | USA | mm/dd/yyyy | 10/27/1991 |
| IBM European standard | EUR | dd.mm.yyyy | 27.10.1991 |
| Japanese Industrial Standard Christian era | JIS | yyyy-mm-dd | 1991-10-27 |
| Site-defined (see DB2 Data Links Manager Quick Beginnings) | LOC | Depends on database country code | -- |
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 may be included; a leading zero may be omitted from the hour part of the time and seconds may be omitted entirely. If seconds are omitted, 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 4. Each format is identified by name and includes an
associated abbreviation and an example of its use.
Table 4. Formats for String Representations of Times
| Format Name | Abbreviation | Time Format | Example |
|---|---|---|---|
| International Standards Organization2 | ISO | hh.mm.ss | 13.30.05 |
| IBM USA standard | USA | hh:mm AM or PM | 1:30 PM |
| IBM European standard | EUR | hh.mm.ss | 13.30.05 |
| Japanese Industrial Standard Christian Era | JIS | hh:mm:ss | 13:30:05 |
| Site-defined (see DB2 Data Links Manager Quick Beginnings) | LOC | Depends on database country code | -- |
Notes: