Datetime Functions

DATE type2 is compatible with all datetime functions and can be used as an argument or return type. The behavior of DATE type2 is similar to the existing DATE datatype for the following datetime functions:

  • DATE_ADD

  • DATEADD

  • DATE_SUB

  • DATEDIFF

  • DATEFORMAT

  • DAY

  • DAYNAME

  • DAYOFMONTH

  • DAYOFWEEK

  • DAYOFYEAR

  • JULIANTIMESTAMP

  • LAST_DAY

  • MONTH

  • MONTHNAME

  • MONTHS_BETWEEN

  • QUARTER

  • TO_CHAR(<DATETIME>)

  • WEEK

  • YEAR

The behavior of DATE type2 differs from the existing DATE datatype for the following datetime functions:

  • CURRENT_DATE

  • EXTRACT

  • MIN

  • MAX

DATE type2 can be used with HOUR, MINUTE, and SECOND functions.

CURRENT_DATE

If the DC_MODE CQD is set to 1, the CURRENT_DATE function returns the local current date as a value of type DATE type2. The output string format depends on the DATE_FORMAT session default setting.

Example 
>>set session default DATE_FORMAT 'yyyy-mm-dd hh24:mi:ss';

--- SQL operation complete.


>>select CURRENT_DATE from (values(1)) as t;
(EXPR)
-------------------
2016-10-24 11:25:30
--- 1 row(s) selected.

EXTRACT

The EXTRACT function extracts the time fields, HOUR, MINUTE, and SECOND from DATE type2.

Example 
>>select CURRENT_DATE, EXTRACT (HOUR FROM CURRENT_DATE) from (values(1)) t;
(EXPR)                (EXPR)
---------------------- ------
2016-10-24 11:25:30    11
--- 1 row(s) selected.

MIN

The MIN function considers the time portion for evaluating the minimum value for DATE type2.

MAX

The MAX function considers the time portion for evaluating the maximum value for DATE type2.