Datetime values

The datetime data types are DATE, TIME, and TIMESTAMP. Although datetime values can be used in certain arithmetic and string operations and are compatible with certain strings, they are not strings or numbers.

Date

A date is a three-part value (year, month, and day):
  • The range of the month part is 1 - 12.
  • The range of the day part is 1 - x, where x is 28, 29, 30, or 31, and depends on the month.
  • The range of the year part is 0001 - 9999 for local tables.

The internal representation of a date is a string of 4 bytes. Each byte consists of 2 packed decimal digits. The first 2 bytes represent the year, the third byte the month, and the last byte the day.

The length of a DATE column, as described in the SQLDA, is 10 bytes, which is the appropriate length for a character string representation of the value.

Time

A time is a three-part value (hour, minute, and second) designating a time of day under a 24-hour clock:
  • The range of the hour part is 0 - 24.
  • The range of the minute and second parts is 0 - 59.
If the hour is 24, the minute and second specifications are 0.
Important: Using the value 24 when representing hours in a time value might result in errors or unexpected data. To avoid this issue, use 00 instead of 24.

The internal representation of a time is a string of 3 bytes. Each byte consists of 2 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 character string representation of the value.

Timestamp

A timestamp is a six or seven-part value (year, month, day, hour, minute, second, and optional fractions of a second) designating a date and time as defined in the previous sections, except that the time can also include an additional part designating a fraction of a second. The number of digits in the fractional seconds is specified using an attribute in the range from 0 to 12; the default is 6.
Important: Using the value 24 when representing hours in a timestamp value might result in errors or unexpected data. To avoid this issue, use 00 instead of 24.

The internal representation of a timestamp is a string of 7 - 13 bytes. Each byte consists of 2 packed decimal digits. The first 4 bytes represent the date, the next 3 bytes the time, and the last 0 - 6 bytes the fractions of a second.

The length of a TIMESTAMP column, as described in the SQLDA, is 19 - 32 bytes, which is the appropriate length for the character string representation of the value.

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. Date, time, and timestamp values can, however, also be represented by strings. This is useful because there are no constants or variables whose data types are DATE, TIME, or TIMESTAMP. Before it can be retrieved, a datetime value must be assigned to a string variable. The CHAR function or the GRAPHIC function (for Unicode databases only) can be used to change a datetime value to a string representation. The string representation is normally the default format of datetime values associated with the territory code of the application, 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 cannot be used as a string representation of a datetime value (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 value before the operation is performed.

Date, time and timestamp strings must contain only characters and digits.

Date strings

Table 1. Formats for String Representations of Dates
Format Name Abbreviation Date Format Example
International Standards Organization (with separators) ISO yyyy-mm-dd '2018-10-27'
International Standards Organization (without separators)1 - yyyymmdd '20181027'
IBM® USA standard USA mm/dd/yyyy '10/27/2018'
IBM European standard EUR dd.mm.yyyy '27.10.2018'
Japanese Industrial Standard Christian Era JIS yyyy-mm-dd '2018-10-27'
Netezza®1 - dd-mon-yy '12-FEB-16'
Site-defined LOC Depends on the territory code of the application -
1 This format can be used for input values only, not for output values or constants.
Note:
  • Trailing blanks can be included.
  • Leading zeros can be omitted from the month and day portions.

Time strings

Table 2. Formats for String Representations of Times
Format Name Abbreviation Time Format Example
International Standards Organization ISO hh.mm or hh.mm.ss '13.30.05'
IBM USA standard USA hh or hh:mm AM or PM '1:30 PM'
IBM European standard EUR hh.mm or hh.mm.ss '13.30.05'
Japanese Industrial Standard Christian Era JIS hh:mm or hh:mm:ss '13:30:05'
Site-defined LOC Depends on the territory code of the application -
Note:
  • Trailing blanks can be included.
  • A leading zero can be omitted from the hour.
  • Seconds can be omitted, in which case an implicit specification of 0 seconds is assumed (for example, 13:30 is equivalent to 13:30:00).
  • The International Standards Organization changed the time format so that it is identical to the Japanese Industrial Standard Christian Era format. Therefore, use the JIS format if an application requires the current International Standards Organization format.
  • In the USA format:
    • Minutes can be omitted, in which case an implicit specification of 00 minutes is assumed (for example, 1 PM is equivalent to 1:00 PM).
    • The hour must not be greater than 12 and cannot be 0, except in the special case of 00:00 AM.
    • There is a single space before 'AM' or 'PM'.
    • 'AM' or 'PM' can be represented in lowercase or uppercase characters.
  • The following table shows how the USA format corresponds to the JIS format:
    USA format JIS format
    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

Timestamp strings

Table 3. Formats for String Representations of Timestamps
Format Name Timestamp Format Example
IBM SQL yyyy-mm-dd-hh.mm.ss.nnnnnnnnnnnn '2018-03-22-12.00.00.000000000005'
ODBC1 yyyy-mm-dd hh:mm:ss.nnnnnnnnnnnn '2018-03-22 08:30:58.000000000005'
Netezza yyyymmdd hh:mm:ss AM or PM '20180101 12:00:59 PM'
Netezza1 mm-dd-yyyy hh:mm:ss.nnnnnnnnnnnn '05-18-2020 18:10:16.123456123456'
No delimiters yyyymmddhhmmss '20180322120000'
1 The date and time portions of the timestamp are shown here as being separated by a blank. They can also be separated by a hyphen or the letter T. For example, '2018-03-22 08:30:58.7', '2018-03-22-08:30:58.7', and '2018-03-22T08:30:58.7' are all equivalent.
Note:
  • IBM SQL is the only supported output format
  • Seconds can be specified to up to 12 decimal places.
  • Trailing zeros can be truncated or entirely omitted from the fractional seconds.
  • Leading zeros can be omitted from the month, day, and hour part of the timestamp.
  • A character string can contain any number of trailing blanks.
  • The separator character that follows the seconds element can be omitted if fractional seconds are not included.
  • If a string representation of a timestamp is implicitly cast to a value with a TIMESTAMP data type, the precision of the result of the cast is determined by the precision of the TIMESTAMP operand in an expression or the precision of the TIMESTAMP target in an assignment:
    • Digits for fractional seconds that exceed the precision of the cast are truncated from the string. For example, if the string '2018-3-2-8.30.00297' is cast to TIMESTAMP(3), the result is 2018-03-02-08.30.00.002.
    • If the precision of the cast exceeds the precision of the string, the result is padded with zeros. For example, if the string '2018-3-2-8.30.07' is cast to TIMESTAMP(12), the result is 2018-03-02-08.30.00.070000000000.
  • A string representation of a timestamp can be given a different timestamp precision by explicitly casting the value to a timestamp with a specified precision. If the string is a constant, an alternative is to precede the string constant with the TIMESTAMP keyword. For example, TIMESTAMP '2018-03-28 14:50:35.123' has the TIMESTAMP(3) data type.