SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—523725-004
6-42
Datetime Value Expressions
Datetime value expressions are built from operands that can be:
•
Interval value expressions
•
Datetime or interval literals
•
Host variables of type DATE, TIME, TIMESTAMP, and INTERVAL
•
Dynamic parameters
•
Column references with datetime or interval values
•
Host variables of type INTERVAL
•
Dynamic parameters
•
Datetime or interval value functions
•
Any aggregate functions, sequence functions, scalar subqueries, CASE
expressions, or CAST expressions that return datetime or interval values
SQL/MP Considerations for Datetime Value Expressions
FRACTION-Only DATETIME Columns
Suppose that an SQL/MP table has a DATETIME column defined as:
MPDateTimeCol DATETIME FRACTION(6)
DEFAULT DATETIME '123456' FRACTION(6)
You cannot use this column in a datetime expression, as a CAST argument, or as an
argument of an aggregate function such as MIN or MAX. NonStop SQL/MX returns an
error indicating that operations with FRACTION-only columns are not supported.
Considerations for Datetime Value Expressions
Data Type of Result
In general, the data type of the result is the data type of the datetime-primary part
of the datetime expression. For example, datetime value expressions include:
The datetime primary in the first expression is CURRENT_DATE, a function that
returns a value with DATE data type. Therefore, the data type of the result is DATE.
CURRENT_DATE + INTERVAL '1' DAY The sum of the current date and an
interval value of one day.
CURRENT_DATE + est_complete The sum of the current date and the
interval value in column
EST_COMPLETE.
( SELECT ship_timestamp
FROM project
WHERE projcode=1000 )
+ INTERVAL '07:04' DAY TO HOUR
The sum of the ship timestamp for
the specified project and an interval
value of seven days, four hours.