SQL Reference Guide > SQL Reference Guide > Understanding SQL Data Types > Literals > Date and Time Literals
Was this helpful?
Date and Time Literals
Date and time literals specify ANSI compliant datetime values. There are four types of date and time literals:
• date
• time
• timestamp
• interval
Date and time literals can be assigned to the corresponding date and time data type without using an explicit conversion function. The value is coded as a quoted string, but is automatically converted to the appropriate internal value.
Date Literals
Literals of the ANSI date type have the following format:
DATE 'date_value'
date_value
Defines a date in the format yyyy-mm-dd.
Note:  The II_DATE_FORMAT setting has no impact on the processing of date literals.
Examples:
date '2012-05-29'
date '1998-10-08'
date '2000-11-29'
Time Literals
Literals of the ANSI time type have the following format:
TIME 'time_value'
time_value
Defines a time value in the format hh:mm:ss, optionally followed by .fff (fractions of seconds) and also optionally followed by ±hh:mm, the time zone offset.
Examples:
time '11:11:00'
time '18:05:23.425364'
time '5:23:00-5:00'
time '18:05:23.4253+08:00'
Time Format
Example
Description
TIME WITHOUT TIME ZONE
TIME '11:11:00'
A time value with no decimal precision
TIME WITHOUT TIME ZONE(6)
TIME '18:05:23.425364'
A time value with six places of decimal precision
TIME WITH TIME ZONE
TIME '05:23:00-5:00'
A time with time zone value with no decimal precision
TIME WITH TIME ZONE(4)
TIME '18:05:23.4253+08:00'
A time with time zone value with four places of decimal precision
Timestamp Literals
Literals of the ANSI timestamp type have the following format:
TIMESTAMP 'timestamp_value'
timestamp_value
Consists of a date value and a time value separated by a single space. The time value can contain optional fractions of seconds, time zone offset, or both.
Examples:
timestamp '2012-05-29 10:30:00.000-04:00'
timestamp '1918-11-11 11:11:00'
Interval Literals
Literals of the ANSI interval type have the following format:
INTERVAL '[sign]interval_value' interval_qualifier
sign
Indicates a positive (+) or negative (-). The default is +.
interval_value
Consists of:
A year to month interval value in the format: year-mm (for example: '25-7') or
A day to second interval value in the format: dddd... hh:mm:ss[.fffffffff] (for example: '15 5:10:27.4325')
interval_qualifier
Qualifies the interval as a year to month or a day to second. The interval_qualifier has the following format:
leading field [TO trailing field]
Valid values for leading field and trailing field in order of precedence are:
YEAR
MONTH
DAY
HOUR
MINUTE
SECOND [(p)].
The leading field cannot have lower precedence than the trailing field.
The precision value on the SECOND field indicates the number of digits allowed in the fractional part of that field.
Examples:
interval'5-7' year to month
interval '-0-11' year to month
interval'+24 12:10:5.1234' day to second
interval '124' year
interval '12' month
interval '18' day
interval '10' hour
interval '34' minute
interval '20.23456789' second (9)
interval '8-11' year to month
interval '12 10' day to hour
interval '12 10:20' day to minute
interval '121 10:15:23.123456' day to second(6)
Last modified date: 04/03/2024