SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—523725-004
6-31
Interval Data Types
point, and the fractional-precision is the number of digits of precision after
the decimal point.
The default for leading-precision is 2, and the default for
fractional-precision is 6. The maximum for leading-precision is 18,
and the maximum for fractional-precision is 6.
Considerations for Interval Data Types
Interval Leading Precision
The maximum for the leading-precision depends on the number of fields in the
interval and on the fractional-precision. The maximum is computed as:
max-leading-precision = 18 - fractional-precision - 2 * (N - 1)
where N is the number of fields in the interval.
For example, the maximum number of digits for the leading-precision in a column
with data type INTERVAL YEAR TO MONTH is computed as: 18 – 0 – 2 * (2 – 1) = 16
Interval Ranges
Within the definition of an interval range (other than a single field), the start-field
and end-field can be any of the specified fields with these restrictions:
•
An interval range is either year-month or day-time—that is, if the start-field is
YEAR, the end-field is MONTH; if the start-field is DAY, HOUR, or
MINUTE, the end-field is also a time field.
•
The start-field must precede the end-field within the hierarchy: YEAR,
MONTH, DAY, HOUR, MINUTE, and SECOND.
Signed Intervals
To include a quoted string in a signed interval data type, the sign must be outside the
quoted string. It can be before the entire literal or immediately before the duration
enclosed in quotes.
For example, for the interval “minus (5 years 5 months) these formats are valid:
INTERVAL - '05-05'YEAR TO MONTH
- INTERVAL '05-05' YEAR TO MONTH
Overflow Conditions
When you insert a fractional value into an INTERVAL data type field, if the fractional
value is 0 (zero) it does not cause an overflow. Inserting value INTERVAL '1.000000'
SECOND(6) into a field SECOND(0) does not cause a loss of value. Provided that the
value fits in the target column without a loss of precision, NonStop SQL/MX does not
return an overflow error.
However, if the fractional value is > 0, an overflow occurs. Inserting value INTERVAL
'1.000001' SECOND(6) causes a loss of value.