Datetime constants

A datetime constant is a character string constant of a particular format.

Character-string constants are described under Character string constants.

For information about the valid string formats, see String representations of datetime values.

Start of change Typically, character-string constants are used to represent constant datetime values in assignments and comparisons. However, the ANSI/ISO SQL standard form of a datetime constant can be used to specifically denote the constant as a datetime constant instead of a character-string constant. The format for the ANSI/ISO SQL standard datetime constants are as follows: End of change

DATE string-constant

string-constant must contain a value that conforms to one of the valid formats for string representations of dates, subject to the following rules:

leading blanks are not allowed.
leading zeros can be omitted from the month and day elements of the date. An implicit specification of 0 is assumed for any digit that is omitted.
leading zeros must be included for the year element of the date.
trailing blanks can be included.

The data type of the value is DATE.

TIME string-constant

string-constant must contain a value that conforms to one of the valid formats for string representations of times, subject to the following rules:

leading blanks are not allowed.
leading zeros can be omitted from the hour elements of the time.
the seconds element of the time can be omitted.
trailing blanks can be included.
if the USA format is not used and the minutes and seconds are all zeros, the hour can be 24.
If the format is USA, the following additional rules apply:
- the minutes element of the time can be omitted. For example, 1 PM is equivalent to 1:00 PM.
- the letters A, M, and P can be specified in lowercase.
- a single blank must precede the AM or PM.
- the hour must not be greater than 12 and cannot be 0 except when the time is specified as 00:00 AM.

An implicit specification of 0 is assumed for any digit that is omitted.

The correspondence between the USA format and the ISO format (24-hour clock) is as follows:

12:01 AM through 12:59 AM correspond to 00.01.00 through 00.59.00
01:00 AM through 11:59 AM correspond to 01.00.00 through 11.59.00
12:00 PM (noon) through 11:59 PM correspond to 12.00.00 through 23.59.00
12:00 AM (midnight) corresponds to 24.00.00
00:00 AM (midnight) corresponds to 00.00.00

The data type of the value is TIME.

TIMESTAMP string-constant

string-constant must contain a value that conforms to one of the formats listed in the following tables, subject to the following rules:

leading blanks are not allowed.
trailing blanks can be included.
leading zeros can be omitted from the month, day, hour, and time zone hour elements of the timestamp. An implicit specification of 0 is assumed for any digit that is omitted.
leading zeros must be included for the minute, second, and time zone minute elements of the timestamp
the hour can be 24 if the minutes, seconds, and any fractional seconds are all zeroes.
the separator character that follows the seconds element can be omitted if fractional seconds are not included.
the number of digits of fractional seconds can vary from 0 to 12. An implicit specification of 0 is assumed if fractional seconds are omitted. The number of digits of fractional seconds determines the precision of the timestamp value.
an optional single blank can be included between the time and the time zone elements.
an optional time zone can be included, in one of the following formats:
- ±th:tm, with values ranging from -24:00 to +24:00. A value of -0:00 is treated the same as +0:00.
- ±th, with values ranging from -24 to +24 (an implicit specification of 00 is assumed for the time zone minute element)
- uppercase Z to specify UTC

The data type of the value depends on the content of the string constant (where p is the number of digits of fractional seconds in the constant):

TIMESTAMP(p) WITHOUT TIME ZONE if the content of the string constant conforms to the rules in the Table 1 table.
TIMESTAMP(p) WITH TIME ZONE if the content of the string constant conforms to the rules in the Table 2 table.

Table 1. Formats used to specify a value for a data type of TIMESTAMP WITHOUT TIME ZONE
Description	TIMESTAMP(0) WITHOUT TIME ZONE ¹^,³	TIMESTAMP(`p`) WITHOUT TIME ZONE ²^,³
Blank between date and time portions and colons in time portion.	yyyy-mm-dd hh:mm:ss yyyy-mm-dd hh:mm:ss.	yyyy-mm-dd hh:mm:ss.nnnnnnnnnnnn
Minus sign between date and time portions and periods in time portion.	yyyy-mm-dd-hh.mm.ss yyyy-mm-dd-hh.mm.ss.	yyyy-mm-dd- hh.mm.ss.nnnnnnnnnnnn
Blank between date and time portions and periods in time portion.	yyyy-mm-dd hh.mm.ss yyyy-mm-dd hh.mm.ss.	yyyy-mm-dd hh.mm.ss.nnnnnnnnnnnn
Notes: No fractional seconds; shown with and without optional trailing period after seconds `p` is the number of digits of fractional seconds. `nnnnnnnnnnnn` can range from 1 to 12 instances of `n` As an additional format, the character T can be substituted as the separator between the date and time portions of the value.

Table 2. Formats used to specify a value for a data type of TIMESTAMP WITH TIME ZONE
Description	TIMESTAMP(0) WITH TIME ZONE¹^,³	TIMESTAMP(`p`) WITH TIME ZONE ²^,³
Blank between date and time portions and colons in time portion, no space between time and time zone.	yyyy-mm-dd hh:mm:ss±th:tm yyyy-mm-dd hh:mm:ss±th yyyy-mm-dd hh:mm:ss.±th:tm yyyy-mm-dd hh:mm:ss.±th	yyyy-mm-dd hh:mm:ss.nnnnnnnnnnnn ±th:tm yyyy-mm-dd hh:mm:ss.nnnnnnnnnnnn ±th
Minus sign between date and time portions and periods in time portion.	yyyy-mm-dd-hh.mm.ss±th:tm yyyy-mm-dd-hh.mm.ss±th yyyy-mm-dd-hh.mm.ss.±th:tm yyyy-mm-dd-hh.mm.ss.±th	yyyy-mm-dd- hh.mm.ss.nnnnnnnnnnnn ±th:tm yyyy-mm-dd- hh.mm.ss.nnnnnnnnnnnn ±th
Blank between date and time portions, colons in time portion, blank between fractional seconds and sign for time zone.	yyyy-mm-dd hh:mm:ss ±th:tm yyyy-mm-dd hh:mm:ss ±th yyyy-mm-dd hh:mm:ss. ±th:tm yyyy-mm-dd hh:mm:ss. ±th	yyyy-mm-dd hh:mm:ss.nnnnnnnnnnnn ±th:tm yyyy-mm-dd hh:mm:ss.nnnnnnnnnnnn ±th
Blank between date and time portions and periods in time portion.	yyyy-mm-dd hh.mm.ss±th:tm yyyy-mm-dd hh.mm.ss±th yyyy-mm-dd hh.mm.ss.±th:tm yyyy-mm-dd hh.mm.ss.±th	yyyy-mm-dd hh.mm.ss.nnnnnnnnnnnn ±th:tm yyyy-mm-dd hh.mm.ss.nnnnnnnnnnnn ±th
Notes: No fractional seconds; shown with and without optional trailing period after seconds `p` is the number of digits of fractional seconds. `nnnnnnnnnnnn` can range from 1 to 12 instances of `n` As an additional format, the character T can be substituted as the separator between the date and time portions of the value.