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

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—540440-003
6-70
Interval Literals
Updating Supported INTERVAL Columns
Suppose that an SQL/MP table has an INTERVAL column defined as:
MPIntervalCol INTERVAL YEAR TO MONTH
DEFAULT INTERVAL '01-03' YEAR TO MONTH
You can insert into this column by using an INTERVAL YEAR TO MONTH literal. For
example:
INSERT INTO MPTable (MPIntervalCol)
VALUES (INTERVAL '01-03' YEAR TO MONTH);
Updating INTERVAL SECOND TO FRACTION Columns
Suppose that an SQL/MP table has an INTERVAL column defined as:
MPIntervalCol INTERVAL SECOND TO FRACTION(1)
DEFAULT INTERVAL '30.0' SECOND TO FRACTION(1)
You can insert into this column by using the equivalent SQL/MX INTERVAL SECOND
literal. For example:
INSERT INTO MPTable (MPIntervalCol)
VALUES (INTERVAL '36.3' SECOND(2,1));
See SQL/MP INTERVAL SECOND TO FRACTION Types on page 6-70.
FRACTION-Only INTERVAL Columns
Suppose that an SQL/MP table has an INTERVAL column defined as:
MPIntervalCol INTERVAL FRACTION(6)
DEFAULT INTERVAL '123456' FRACTION(6)
You cannot insert into tables with unsupported FRACTION-only INTERVAL columns
because you cannot specify values for these columns. Therefore, you must populate
tables with columns of this type by using SQL/MP instead of NonStop SQL/MX.
You can select data from an INTERVAL column. See Selecting INTERVAL Columns in
SQL/MP Tables on page 6-30.
SQL/MP INTERVAL SECOND TO FRACTION Types
You must use the equivalent SQL/MX INTERVAL SECOND literal to insert into or
update an SQL/MP INTERVAL SECOND TO FRACTION column. The equivalent
mappings are:
SQL/MP Start Field SQL/MP End Field Equivalent SQL/MX Type
SECOND SECOND or none SECOND(2,0)
SECOND(x) SECOND or none SECOND(x,0)
SECOND FRACTION SECOND(2,6)
SECOND(x) FRACTION SECOND(x,6)
SECOND(x) FRACTION(y) SECOND(x,y)
SECOND FRACTION(y) SECOND(2,y)