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

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—540440-003
6-28
Interval Data Types
Suppose that the current timestamp is 2000-01-26:10:24:10.212072. This expression
involves extension on both ends:
CAST(DATETIME '12-23' MONTH TO DAY AS TIMESTAMP)
The result of the CAST is 2000-12-23:00:00:00.000000.
Operations Equivalent to UNITS
The SQL/MP UNITS operator is not supported. However, NonStop SQL/MX does
support equivalent syntax.
Suppose that an SQL/MP table has a DATETIME column defined as:
MPDateTimeCol DATETIME MONTH TO DAY
DEFAULT DATETIME '03-12' MONTH TO DAY
Using this column as an example, you can specify this equivalent:
Interval Data Types
Considerations for Interval Data Types
SQL/MP Considerations for Interval Data Types
Values of interval data type represent durations of time in year-month units (years and
months) or in day-time units (days, hours, minutes, seconds, and fractions of a
second).
INTERVAL { start-field TO end-field | single-field }
specifies a column that represents a duration of time as either a year-month or
day-time range or a single-field. The optional sign indicates if this is a positive or
negative integer. If you omit the sign, it defaults to positive.
If the interval is specified as a range, the start-field and end-field must be
in one of these categories:
SQL/MP Element SQL/MX Equivalent
MPDateTimeCol UNITS MONTH MONTH(MPDateTimeCol)
interval-type is:
INTERVAL { start-field TO end-field | single-field }
start-field is:
{YEAR | MONTH | DAY | HOUR | MINUTE} [(leading-precision)]
end-field is:
YEAR | MONTH | DAY | HOUR | MINUTE | SECOND
[(fractional-precision)]
single-field is:
start-field | SECOND [(leading-precision,
fractional-precision)]