SQL/MX 2.x Database and Application Migration Guide (G06.23+, H06.04+, J06.03+)

Converting SQL/MP Applications to SQL/MX
Applications
HP NonStop SQL/MX Database and Application Migration Guide—540435-005
10-36
Date-Time Data Types and Literals
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.
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
-------------
######
######
...
NonStop SQL/MX returns a warning indicating that you selected an unsupported data
type with undefined contents. However, if you try to use the unsupported data type in
any sort of expression, NonStop SQL/MX returns an error.
The SQL/MP DATETIME columns that do not map to standard SQL/MX types are
represented as SQL/MP DATETIME types in NonStop SQL/MX. These types are listed
in Table 10-9
.
In embedded SQL, the method for declaring input and output host variables with
date-time data types for NonStop SQL/MX is different from the SQL/MP method. For
more information about host variables, see Host Variables With Date-Time Data Types
on page 10-44.
Date-Time Literals
In NonStop SQL/MP, a date-time literal is a DATETIME, DATE, TIME, or TIMESTAMP
literal that you can use in an expression and which can appear in default, USA, or
European format.
In NonStop SQL/MX, a date-time literal is a DATE, TIME, or TIMESTAMP literal. An
SQL/MX date-time literal begins with the DATE, TIME, or TIMESTAMP keyword and
can appear in default, USA, or European format.
For example, these DATE literals are in default, USA, and European formats,
respectively:
DATE '1990-01-22'
DATE '01/22/1990'
DATE '22.01.1990'