SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Creating an SQL/MX Database
HP NonStop SQL/MX Installation and Management Guide—523723-004
7-27
Managing Table Data
•
The range of fields defined for an INTERVAL column can limit the value stored, as
shown:
•
The fields in a datetime or INTERVAL value have this implied order: YEAR,
MONTH, DAY, HOUR, MINUTE, SECOND.
•
Possible default values for an INTERVAL column are:
•
You cannot use a datetime column with other SQL/MX data types in arithmetic
expressions or comparisons, except for interval or datetime data types. This table
shows the results of arithmetic operations involving datetime and interval values:
•
A negative value is not a valid entry for a datetime column. An interval column,
however, can contain negative values.
•
Interval values can be multiplied or divided by positive or negative numeric values.
•
The result of adding or subtracting two INTERVAL values is an INTERVAL value.
•
The result of adding or subtracting an INTERVAL value to or from a datetime value
is a datetime value.
•
Carries and borrows can occur when adding or subtracting an INTERVAL value to
or from a datetime value. For example, subtracting INTERVAL ‘1’ MONTH from
TIMESTAMP ‘2004-01-01 12:00:00’ results in TIMESTAMP ‘2003-12-31 12:00:00’.
That is, the month field is changed and a borrow occurs from the year field.
•
If an arithmetic expression causes invalid data, an SQL/MX error is generated.
COLUMN_1 INTERVAL YEAR
COLUMN_2 INTERVAL HOUR (3)
COLUMN_3 INTERVAL YEAR TO MONTH
COLUMN_4 INTERVAL DAY TO MINUTE
DEFAULT interval-literal A valid INTERVAL literal
DEFAULT NULL Initialized to the null value
First Operand Operator
Second
Operator Result
Datetime - Datetime INTERVAL
Datetime + or - INTERVAL Datetime
INTERVAL + Datetime Datetime
INTERVAL + or - INTERVAL INTERVAL
INTERVAL * or / Numeric INTERVAL
Numeric * INTERVAL INTERVAL
INTERVAL / INTERVAL Numeric