Data Transformation Engine SQL/MX Adapter Reference Guide

Date and Time Formats Chapter 3 - Database Columns and Types
SQL/MX Adapter Reference Guide
9
Date and Time Formats
There are two different categories of date and time formats for the NonStop
SQL/MX adapter:
♦ default
♦ available
Note If any of your tables contain a datetime column, or the columns you are selecting
for a type tree are datetime, you need to use mtsmaker to generate your type
trees. (The available section identifies the various SQL/MX-specific types.)
Otherwise, ODBC-generated trees are compatible with the NonStop SQL/MX
adapter (as described in the default section).
Default NonStop SQL/MX Format
When using columns defined as dates and times in NonStop SQL/MX databases,
the default format for both input and output is:
datetime:
ccyy-mm-dd:hh:mm:ss.ffffff
where
cc
= a two-digit century
yy
= a two-digit year
mm
= a two-digit month
dd
= a two-digit day
hh
= a two-digit hour
mm
= a two-digit minute
ss
= a two-digit second
ffffff
= fractional seconds
An example is 2001-08-27:00:00:00.000000 to specify August 27, 2001.
Note In the Generate Type Tree from dialog box in the Database Interface Designer,
use the Represent date/time columns as text items check box to define
whether to automatically format this information as Date & Time (which is with
this check box disabled, the result of which is shown in the Item Subclass,
Presentation column above) as a text string. When generated as a text string, it
may be necessary to use either the TEXTTODATE or TEXTTOTIME function in a
map rule to convert the text string to the date and time format required by the
database. If you are generating new type trees, it is recommended that you
disable this check box.