DATE type2

The DATE type2 datatype is supported by:

• All SQL/MX Utilities

• Windows ANSI and UNICODE ODBC/MX drivers

• JDBC T2 and T4 drivers

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.

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: For DATE type2 columns, DATE portion must be provided in DEFAULT and FIRST KEY clause of DDL statements.

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.

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.

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.