Neoview SQL Reference Manual (R2.4 SP2)

Considerations for Interval Data Types
Adding or Subtracting Imprecise Interval Values
Adding or subtracting an interval that is any multiple of a MONTH, a YEAR, or a combination
of these may result in a runtime error. For example, adding 1 MONTH to January 31, 2009 will
result in an error because there is no February 31 and it is not clear whether the user would want
rounding back to February 28, 2009, rounding up to March 1, 2009 or perhaps treating the interval
1 MONTH as if it were 30 days resulting in an answer of March 2, 2009. Similarly, subtracting 1
YEAR from February 29, 2008 will result in an error. See the descriptions for the “ADD_MONTHS
Function” (page 334), “DATE_ADD Function” (page 367), “DATE_SUB Function” (page 368), and
“DATEADD Function” (page 369) for ways to add or subtract such intervals without getting
errors at runtime.
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, Neoview SQL 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.
240 SQL Language Elements