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

Introduction
HP NonStop SQL/MX Reference Manual—540440-003
1-27
Selecting or Changing Data
Standard DATETIME Data Types
Certain DATETIME data types map directly to the ANSI standard types—DATE, TIME,
and TIMESTAMP. You can retrieve the same value as stored in an SQL/MP column
with these data types or store a value with a standard type into these SQL/MP columns
without truncation or extension.
For more information, see:
•
SQL/MP Considerations for Datetime Data Types Not Equivalent to DATE, TIME,
TIMESTAMP on page 6-24
•
SQL/MP Considerations for Datetime Data Types Equivalent to DATE, TIME,
TIMESTAMP on page 6-26
Truncation and Extension
If you attempt to insert a larger DATETIME value into a smaller DATETIME column,
NonStop SQL/MX implicitly truncates the value only on the fractional part. In all other
cases, NonStop SQL/MX returns an error. If you attempt to insert a smaller DATETIME
value into a larger DATETIME column, NonStop SQL/MX returns an error.
When you are storing values in a DATETIME column, you must explicitly cast the
DATETIME value in question to the desired DATETIME data type to ensure
compatibility. 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.
When you are comparing datetime data with different start and end fields in a WHERE
clause, you must also specify an explicit CAST to ensure compatibility.
See Casting DATETIME Data for Compatibility on page 6-27.
Using Datetime Functions
You can use SQL/MX datetime functions to select individual fields from a DATETIME
column in an SQL/MP table.
See Using SQL/MX Datetime Functions on DATETIME Data on page 6-27.
Selecting Any DATETIME Column
You can select data from any DATETIME column except those consisting of
FRACTION only.
This ANSI
standard data
type: is equivalent to this SQL/MP data type:
DATE DATETIME YEAR TO DAY
TIME DATETIME HOUR TO SECOND
TIMESTAMP DATETIME YEAR TO SECOND