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

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—540440-003
6-65
Datetime Literals
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
in an SQL/MP table. The literal is written:
DATETIME 'datetime' [start-field TO] end-field
The string literal 'datetime' is a subset of the standard datetime form:
'yyyy-mm-dd:hh:mm:ss.msssss'
The literal is followed by the qualifier, consisting of an optional start field and an end
field. The qualifier has a range of logically contiguous fields in this order: YEAR,
MONTH, DAY, HOUR, MINUTE, SECOND, and FRACTION. NonStop SQL/MX
supports all SQL/MP DATETIME literals except those consisting of FRACTION only.
Nonstandard Datetime Literal Fields
NonStop SQL/MX requires that the individual fields of a DATETIME literal have the
specified standard lengths. For example, this literal is not supported because the hour
field is not two digits:
TIME '1:40:05'
For NonStop SQL/MX, use:
TIME '01:40:05'
Inserting Into or Updating Supported DATETIME Columns
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 insert into this column by using a DATETIME MONTH TO DAY literal. For
example:
INSERT INTO MPTable (MPDateTimeCol)
VALUES (DATETIME '04-15' MONTH TO DAY);
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 insert into tables with unsupported FRACTION-only DATETIME columns
because you cannot specify values for these columns. Therefore, tables with columns
of this type must be populated by using NonStop SQL/MP instead of
NonStop SQL/MX.
You can select data from a DATETIME column. See Selecting DATETIME Columns in
SQL/MP Tables on page 6-25.