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

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—540440-003
6-27
Datetime Data Types
Using SQL/MX Datetime Functions on DATETIME Data
You can use SQL/MX datetime functions to select individual fields from a DATETIME
column in an SQL/MP table. For example, suppose that an SQL/MP table has a
DATETIME column defined as:
MPDateTimeCol DATETIME MONTH TO DAY
DEFAULT DATETIME '03-12' MONTH TO DAY
You can select the month from this column:
SELECT MONTH(MPDateTimeCol) FROM MPTable;
(EXPR)
-------
...
3
...
See Datetime Functions on page 9-4.
Casting DATETIME Data for Compatibility
DATETIME data types are compatible only if the types have the same start and end
fields. No implicit extension or truncation is performed. If the data does not have the
same start and end fields, you must use CAST to provide an explicit conversion that
allows you to operate on different DATETIME data types.
Overlapping Fields Requirement
You can use CAST provided that the two DATETIME values have at least one
overlapping field. This specification is valid because the types overlap on the DAY field:
CAST(DATE '2000-03-31' AS DATETIME DAY TO HOUR)
However, this specification is not valid because no fields overlap:
CAST(DATETIME '2000-03' YEAR TO MONTH AS TIME)
Extension Resulting From CAST
Suppose that an SQL/MP table has a DATETIME column defined as:
MPDateTimeCol DATETIME MONTH TO DAY
DEFAULT DATETIME '03-12' MONTH TO DAY
Use CAST to compare data:
SELECT * FROM MPTable
WHERE CAST(MPDateTimeCol AS DATE) > CURRENT_DATE;
If extension occurs on the more significant end of a value, the values for the missing
fields are drawn from the fields of CURRENT_TIMESTAMP. If extension occurs on the
less significant end, the values are the minimum field values. In this example, the
YEAR field is from the YEAR field of CURRENT_TIMESTAMP.