Interval Literals

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 literals is 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 “Date, Time, and Timestamp 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>.

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 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}

Literal escape clause Interval specified
{INTERVAL '326' YEAR(4)} Specifies an interval of 326 years. The interval leading precision is 4.
{INTERVAL '326' MONTH(3)} Specifies an interval of 326 months. The interval leading precision is 3.
{INTERVAL '3261' DAY(4)} Specifies an interval of 3261 days. The interval leading precision is 4.
{INTERVAL '163' HOUR(3)} Specifies an interval of 163 days. The interval leading precision is 3.
{INTERVAL '163' MINUTE(3)} Specifies an interval of 163 minutes. The interval leading precision is 3.
{INTERVAL '223.16' SECOND(3,2)} Specifies an interval of 223.16 seconds. The interval leading precision is 3 and the seconds precision is 2.
{INTERVAL '163-11' YEAR(3) TO MONTH} Specifies an interval of 163 years and 11 months. The interval leading precision is 3.
{INTERVAL '163 12' DAY(3) TO HOUR} Specifies an interval of 163 days and 12 hours. The interval leading precision is 3.
{INTERVAL '163 12:39' DAY(3) TO MINUTE} Specifies an interval of 163 days, 12 hours, and 39 minutes. The interval leading precision is 3.
{INTERVAL '163 12:39:59.163' DAY(3) TO SECOND(3)} Specifies an interval of 163 days, 12 hours, 39 minutes, and 59.163 seconds. The interval leading precision is 3, and the seconds precision is 3.
{INTERVAL '163:39' HOUR(3) TO MINUTE} Specifies an interval of 163 hours and 39 minutes. The interval leading precision is 3.
{INTERVAL '163:39:59.163' HOUR(3) TO SECOND(4)} Specifies an interval of 163 hours, 39 minutes, and 59.163 seconds. The interval leading precision is 3, and the seconds precision is 4.
{INTERVAL '163:59.163' MINUTE(3) TO SECOND(5)} Specifies an interval of 163 minutes and 59.163 seconds. The interval leading precision is 3, and the seconds precision is 5.
{INTERVAL -'16 23:39:56.23' DAY TO SECOND} Specifies an interval of minus 16 days, 23 hours, 39 minutes, and 56.23 seconds. The implied leading precision is 2 and the implied seconds precision is 6.

The following table lists examples of invalid interval literals:

Literal escape clause Reason why invalid
{INTERVAL '163' HOUR(2)} The interval leading precision is 2, but the value of the leading field is 163.
{INTERVAL '223.16' SECOND(2,2)}

{INTERVAL '223.16' SECOND(3,1)}

In the first example, the leading precision is too small, and in the second example, the seconds precision is too small.
{INTERVAL '223.16' SECOND}

{INTERVAL '223' YEAR}

Since the leading precision is unspecified, it defaults to 2, which is too small to hold the specified literal.
{INTERVAL '22.1234567' SECOND} The seconds precision is unspecified and so defaults to 6. The literal has seven digits after the decimal point.
{INTERVAL '163-13' YEAR(3) TO MONTH}

{INTERVAL '163 65' DAY(3) TO HOUR}

{INTERVAL '163 62:39' DAY(3) TO MINUTE}

{INTERVAL '163 12:125:59.163' DAY(3) TO SECOND(3)}

{INTERVAL '163:144' HOUR(3) TO MINUTE}

{INTERVAL '163:567:234.163' HOUR(3) TO SECOND(4)}

{INTERVAL '163:591.163' MINUTE(3) TO SECOND(5)}

The trailing field does not follow the rules of the Gregorian Calendar.