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

Introduction
HP NonStop SQL/MX Reference Manual—523725-004
1-26
Selecting or Changing Data
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-28.
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-28.
Selecting Any DATETIME Column
You can select data from any DATETIME column except those consisting of
FRACTION only.
If you attempt to select data from a FRACTION-only column, the value is returned as
the CHAR data type consisting of a string of '#' characters with the same display
length as the length of the column.
See Selecting DATETIME Columns in SQL/MP Tables on page 6-26.
Inserting or Updating Any DATETIME Column
NonStop SQL/MX supports inserting into or updating any columns with the DATETIME
data type in SQL/MP tables except those consisting of FRACTION only. Use a special
SQL/MX DATETIME literal to insert into or update a DATETIME column. The literal is
specified:
DATETIME 'datetime' [start-field TO] end-field
See Inserting Into or Updating Any SQL/MP DATETIME Column on page 6-66.
INTERVAL Data
SQL/MP INTERVAL values represent durations of time in year-month units (years and
months), in day-time units (days, hours, minutes, seconds, and fractions of a second),
or in subsets of those units.
Year-Month Interval
Specify a year-month duration:
INTERVAL start-ym [(digits)] [TO end-ym]