2. SQL Data Types : Literals : Date and Time Literals : Interval Literals
 
Share this page                  
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)