ODBC requires that all drivers support conversion of the SQL_CHAR or SQL_VARCHAR data type to all C interval data types. If the underlying data source does not support interval data types, however, the driver needs to know the correct format of the value in the SQL_CHAR field in order to support these conversions. Similarly, ODBC requires that any ODBC C type be convertible to SQL_CHAR or SQL_VARCHAR, so a driver needs to know what format an interval stored in the character field should have. This section describes the syntax of interval literals, which the driver writer needs to use to validate the SQL_CHAR fields during conversion either to or from C interval data types.
Note The complete BNF for interval literalsis shown in Appendix C,
“SQL Grammar.”
To pass interval literals as part of an SQL statement, an escape clause syntax is defined for interval literals. For more information, see “Datetime Interval Literals” in Chapter 8, “SQL Statements.”
An interval literal is of the form:
INTERVAL [<sign>] ‘value’ <interval qualifier>
where “INTERVAL” indicates that the character literal is an interval. The sign can be either plus or minus; it is outside the interval string and is optional.
The interval qualifier can either be a single datetime field or be composed of two datetime fields, in the form: <leading field> TO <trailing field>.
When the interval is composed of a single field, the single field can be a
non-second field that may be accompanied by an optional leading precision in
parentheses. The single datetime field can also be a second field that may be
accompanied by the optional leading precision, the optional fractional-seconds
precision in parentheses, or both. If both a leading precision and a
fractional-seconds precision are present for a seconds field, they are separated by commas. If
the seconds field has a fractional-seconds precision, it must also have a
leading precision.
When the interval is composed of leading and trailing fields, the leading
field is a non-second field that may be accompanied by the interval leading-field
precision in parentheses. The trailing field can be either a non-second field,
or a second field that may be accompanied by an interval fractional-seconds
precision in parentheses.
The interval string in value is enclosed in single quotation marks. It can be either a year-month literal or a day-time literal. The format of the string in value is determined by the following rules:
The string contains a decimal value for every field that is implied by the
<interval qualifier>.
If the interval precision includes the fields YEAR and MONTH, then the values
of these fields are separated by a minus sign.
If the interval precision includes the fields DAY and HOUR, then the values of
these fields are separated by a space.
If the interval precision includes the field HOUR and the lower order fields
(MINUTE and SECOND), then the values of these fields are separated by a colon
(:).
If the interval precision includes a SECOND field and the expressed or implied
seconds precision is non-zero, then the character immediately before the first
digit of the fractional part of the second is a period (.).
No field can be more than two digits long, except:
The value of the leading field can be as long as the expressed or implied
interval leading precision.
The fractional part of the SECOND field can be as long as the expressed or
implied seconds precision.
The trailing fields follow the usual constraints of the Gregorian calendar
(see
The following table lists examples of valid interval literals as included in the ODBC escape clause for intervals. The syntax of the escape clause is as follows:
{INTERVAL sign interval-string interval-qualifier}