Interval literals provide a means for declaring interval values in expressions.
Interval literals differ from other SQL literals in that keywords introduce and follow them.
General Syntax
INTERVAL [ sign ] 'string' interval_qualifier
- sign
- An optional minus sign to designate a negative interval. The default is a positive interval.
Note that the sign must be outside the apostrophes that enclose string.
- string
- A character string. Spaces and new line characters are not allowed between the apostrophes.
- interval_qualifier
- A keyword or keywords indicating the Interval literal type. Possible values are:
- YEAR
- YEAR TO MONTH
- MONTH
- DAY
- DAY TO HOUR
- DAY TO MINUTE
- DAY TO SECOND
- HOUR
- HOUR TO MINUTE
- HOUR TO SECOND
- MINUTE
- MINUTE TO SECOND
- SECOND
You cannot specify precision with the interval_qualifier.
Interval Literal Categories
Interval literals fall under one of two categories. You cannot mix literals that fall under one category with literals that fall under the other category.
- Year-Month
- Day-Time
Year-Month Literals Day-Time Literals - YEAR
- YEAR TO MONTH
- MONTH
- DAY
- DAY TO HOUR
- DAY TO MINUTE
- DAY TO SECOND
- HOUR
- HOUR TO MINUTE
- HOUR TO SECOND
- MINUTE
- MINUTE TO SECOND
- SECOND
Interval Literal Data Types
The data type for a literal is derived directly from the interval_qualifier.
You cannot specify precision with any of the interval_qualifier types.