DATE type2

DATE type2 is a new datatype representing a date and time value. It represents the date using year, month, day, hour, minute, and second values. The storage size is 7 bytes. The SQL/MX implementation of DATE datatype represents the date format using year, month, and day values. The storage size is 4 bytes. The syntax for using DATE type2 is the same as DATE. The default display format is 'YYYY-MM-DD'. A new session default DATE_FORMAT is introduced to change the display format of DATE type2 datatype.

Applications can switch to database compatibility mode by setting the DC_MODE CQD to 1. In database compatibility mode, the keyword DATE denotes DATE type2 datatype in DDL, DML statements, and Utility commands.

The DATE type2 datatype is supported by:
  • All SQL/MX Utilities

  • Windows ANSI and UNICODE ODBC/MX drivers

  • JDBC T2 and T4 drivers

Embedded C/C++ and COBOL applications, the OSS, Linux, and HP-UX ODBC/MX drivers do not support DATE type2 datatype.

The following example sets DC_MODE CQD to 1, creates a table with DATE type2 column, and inserts a value:
-- DC_MODE default attribute is set to 1.
>>control query default DC_MODE '1';

--- SQL operation complete.

-- Create a table with a DATE type2 column 
>>create table dc_testtab1 (col1 DATE);

--- SQL operation complete.

-- DATE type2 column accepts date and time values as shown below
>>insert into dc_testtab1 values (DATE '2016-09-23 11:12:30'); 

--- 1 row(s) inserted.

Datetime Literals

The datetime literal accepts values with date and time fields for DATE type2. If the DATE portion is not specified, the default value is the first day of the current month. If the TIME portion is not specified, the default value is 00:00:00 A.M. The following table lists a few examples for datetime literal interpretation:
User provided literal DATE type2 literal
DATE '2016-10-11 13:20:10' DATE '2016-10-11 13:20:10'
DATE '13:20:10' DATE '2017-02-01 13:20:10'
DATE '2016-10-11' DATE '2016-10-11 00:00:00'
For DATE type2 columns, DATE portion must be provided in DEFAULT and FIRST KEY clause of DDL statements.

Arithmetic operations

The DATE type2 datatype follows all arithmetic semantics as SQL/MX DATE. However, time portion is always considered during evaluation of DATE type2 values. The result of subtraction between two DATE type2 values is number of days and the return type is NUMERIC (18, 6). The following example sets the DATE_FORMAT session default and illustrates the result of subtracting a DATE type2 value from another:
>>control query default DC_MODE ‘1’;

--- SQL operation complete.

>>create table t1(a DATE, b DATE);

--- SQL operation complete.

>>set session default DATE_FORMAT 'YYYY-MM-DD HH24:MI:SS';

--- SQL operation complete.

>>select a, b, a-b from t1;
A                    B                    (EXPR)
-------------------  -------------------  ---------------------
2016-10-25 11:10:20  2016-10-24 10:10:20               1.041666

--- 1 row(s) selected.
Subtraction between DATE type2 and TIMESTAMP datatype is supported. The return value is of the type INTERVAL SECOND.

Relational and assignment operations

When comparing one DATE type2 to another DATE type2, all fields will be compared including the time portion. For example,
Consider D1= 2016-06-06 17:38:43 and D2=2016-06-06 17:40:02. 
Comparing D1 and D2, 
    D1 = D2 returns FALSE, 
    D1 > D2 returns FALSE and 
    D1 < D2 returns TRUE 
The DATE type2 values can be assigned to and compared with TIMESTAMP columns, or literal, and vice versa.

CAST expression

An expression can now be cast to DATE type2 datatype. The valid conversions for CAST include:
  • A DATE type2 value to a character string or to a TIMESTAMP.

  • A TIMESTAMP value to a DATE type2.

A cast of TIME value to DATE type2 is not supported.


  • The DATE and the DATE type2 cannot exist within the same application.

  • A table can have columns of either DATE datatype or DATE type2 datatype, but not both.

  • The queries using the DATE type2 datatype cannot be cached by MFC.

  • Use of DATE type2 datatype in SPJRS requires a cast to TIMESTAMP(0) or CHAR of appropriate length.

  • SYSDATE, SYSTIMESTAMP, and INSTR are reserved words in SQL/MX 3.5 and cannot be used as identifiers. Use the reserved words within double-quotes to treat them as identifiers.


Online utility operations on table or index having DATE type2 column is not supported.