SQL/MX 2.x Database and Application Migration Guide (G06.23+, H06.04+, J06.03+)

Converting SQL/MP Applications to SQL/MX
Applications
HP NonStop SQL/MX Database and Application Migration Guide—540435-005
10-39
Interval Data Types and Literals
NonStop SQL/MX handles these interval data types in the same manner as NonStop
SQL/MP:
•
YEAR TO (YEAR, MONTH)
•
MONTH TO MONTH
•
DAY TO (DAY, HOUR, MINUTE)
•
HOUR TO (HOUR, MINUTE)
•
MINUTE TO MINUTE
For precision and default of interval data types, NonStop SQL/MP and NonStop
SQL/MX are the same. For additional information about interval data types, see the
SQL/MX Reference Manual.
Selecting FRACTION-Only Interval Columns
If you attempt to select data from a FRACTION-only interval column, the value is
returned as a string of '#' characters with the same display length as the length of the
column.
Suppose that an SQL/MP table has an INTERVAL column defined as:
MPIntervalCol INTERVAL FRACTION(6)
DEFAULT INTERVAL '123456' FRACTION(6)
You cannot select the data from this column. For example, if you enter this statement:
SELECT MPIntervalCol FROM MPTable;
MPIntervalCol
-------------
######
######
...
Table 10-10. Mapping Between Interval Data Types in NonStop SQL/MP and
NonStop SQL/MX
SQL/MP Interval Type Mapping in NonStop SQL/MX
INTERVAL DAY(n) TO SECOND INTERVAL DAY(n) TO SECOND(0)
INTERVAL DAY(n) TO FRACTION(f) INTERVAL DAY(n) TO SECOND(f)
INTERVAL HOUR(n) TO SECOND INTERVAL HOUR(n) TO SECOND(0)
INTERVAL HOUR(n) TO FRACTION(f) INTERVAL HOUR(n) TO SECOND(f)
INTERVAL MINUTE(n) TO SECOND INTERVAL MINUTE(n) TO SECOND(0)
INTERVAL MINUTE(n) TO FRACTION(f) INTERVAL MINUTE(n) TO SECOND(f)
INTERVAL SECOND(n) TO SECOND INTERVAL SECOND(n,0)
INTERVAL SECOND(n) TO FRACTION(f) INTERVAL SECOND(n,f)
INTERVAL FRACTION(n) TO FRACTION(f) Not supported