SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—540440-003
6-30
Interval Data Types
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.
SQL/MP Considerations for Interval Data Types
Selecting INTERVAL Columns in SQL/MP Tables
SQL/MP INTERVAL values represent durations of time in year-month units (years and
months), in day-time units (days, hours, minutes, seconds, and fractions of a second),
or in subsets of those units.
A specific INTERVAL data type consists of a subset or range of these fields and a
specified number of significant digits for the FRACTION field if it exists. For example:
INTERVAL YEAR TO MONTH
INTERVAL DAY TO FRACTION(3)
Selecting Supported INTERVAL Columns
NonStop SQL/MX supports accessing any INTERVAL column—except those
consisting of FRACTION only. For example, suppose that an SQL/MP table has an
INTERVAL column defined as:
MPIntervalCol INTERVAL SECOND(2) TO FRACTION(1)
DEFAULT INTERVAL '36.8' SECOND(2) TO FRACTION(1)
You can select from this column as shown:
SELECT MPIntervalCol FROM MPTable;
MPIntervalCol
-------------
...
36.8
...
In this example, the SQL/MP column of type INTERVAL SECOND(2) TO
FRACTION(1) is interpreted in NonStop SQL/MX as type INTERVAL SECOND(2,1).