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

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—540440-003
6-25
Datetime Data Types
•
DATETIME HOUR TO FRACTION(n)
•
DATETIME MINUTE
•
DATETIME MINUTE TO SECOND
•
DATETIME MINUTE TO FRACTION(n)
•
DATETIME SECOND
•
DATETIME SECOND TO FRACTION(n)
Selecting DATETIME Columns in SQL/MP Tables
The SQL/MP DATETIME data type has a range of logically contiguous fields in this
order: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, and FRACTION.
A specific DATETIME data type consists of a subset or range of these fields and a
specified number of significant digits for the FRACTION field. For example:
DATETIME YEAR TO MONTH
DATETIME DAY TO FRACTION(3)
The qualifier that specifies the range of fields for the DATETIME data type has the
same syntax as the qualifier that specifies the range of fields for the INTERVAL data
type.
Selecting Supported DATETIME Columns
NonStop SQL/MX supports accessing any SQL/MP DATETIME column—except those
consisting of FRACTION only. 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 from this column as shown:
SELECT MPDateTimeCol FROM MPTable;
MPDateTimeCol
-------------
...
03-12
...
Selecting FRACTION-Only DATETIME Columns
If you attempt to select data from a FRACTION-only DATETIME column, the value is
returned as a string of '#' characters with the same display length as the length of the
column. For example, suppose that an SQL/MP table has a DATETIME column
defined as:
MPDateTimeCol DATETIME FRACTION(6)
DEFAULT DATETIME '123456' FRACTION(6)
You cannot select the data from this column. For example:
SELECT MPDateTimeCol FROM MPTable;
MPDateTimeCol