>>
>>select to_date('2016-03-01', 'YYYY-MM-DD') from (values(1)) x(a);

(EXPR)    
----------

2016-03-01

--- 1 row(s) selected.
>>select to_date('03/01/2016', 'mm/dd/yyyy') from (values(1)) x(a);

(EXPR)    
----------

2016-03-01

--- 1 row(s) selected.
>>select to_date('01.03.2016', 'DD.MM.YYYY') from (values(1)) x(a);

(EXPR)    
----------

2016-03-01

--- 1 row(s) selected.
>>select to_date('2016-03', 'YYYY-MM') from (values(1)) x(a);

(EXPR)    
----------

2016-03-01

--- 1 row(s) selected.
>>select to_date('2016/03/01', 'YYYY/MM/DD') from (values(1)) x(a);

(EXPR)    
----------

2016-03-01

--- 1 row(s) selected.
>>select to_date('20160301', 'YYYYMMDD') from (values(1)) x(a);

(EXPR)    
----------

2016-03-01

--- 1 row(s) selected.
>>select to_date('16/03/01', 'YY/MM/DD') from (values(1)) x(a);

(EXPR)    
----------

0016-03-01

--- 1 row(s) selected.
>>select to_date('03/01/16', 'MM/DD/YY') from (values(1)) x(a);

(EXPR)    
----------

0016-03-01

--- 1 row(s) selected.
>>select to_date('03-01-2016', 'MM-DD-YYYY') from (values(1)) x(a);

(EXPR)    
----------

2016-03-01

--- 1 row(s) selected.
>>select to_date('201603', 'YYYYmm') from (values(1)) x(a);

(EXPR)    
----------

2016-03-01

--- 1 row(s) selected.
>>select to_date('01-03-2016', 'DD-MM-YYYY') from (values(1)) x(a);

(EXPR)    
----------

2016-03-01

--- 1 row(s) selected.
>>select to_date('01-MAR-2016', 'DD-MON-YYYY') from (values(1)) x(a);

(EXPR)    
----------

2016-03-01

--- 1 row(s) selected.
>>select to_date('01MAR2016', 'DDMONYYYY') from (values(1)) x(a);

(EXPR)    
----------

2016-03-01

--- 1 row(s) selected.
>>select to_date('20160301101112', 'YYYYMMDDHH24MISS') from (values(1)) x(a);

(EXPR)                    
--------------------------

2016-03-01 10:11:12.000000

--- 1 row(s) selected.
>>select to_date('01.03.2016 10.11.12', 'DD.MM.YYYY HH24.MI.SS') from (values(1)) x(a);

(EXPR)                    
--------------------------

2016-03-01 10:11:12.000000

--- 1 row(s) selected.
>>select to_date('2016-03-01 10:11:12', 'YYYY-MM-DD HH24:MI:SS') from (values(1)) x(a);

(EXPR)                    
--------------------------

2016-03-01 10:11:12.000000

--- 1 row(s) selected.
>>select to_date('03/01/2016 10:11:12', 'MM/DD/YYYY HH24:MI:SS') from (values(1)) x(a);

(EXPR)                    
--------------------------

2016-03-01 10:11:12.000000

--- 1 row(s) selected.
>>select to_date('01-MAR-2016 10:11:12', 'DD-MON-YYYY HH:MI:SS') from (values(1)) x(a);

(EXPR)                    
--------------------------

2016-03-01 10:11:12.000000

--- 1 row(s) selected.
>>select to_date('March 01, 2016, 10:11', 'MONTH DD, YYYY, HH:MI') from (values(1)) x(a);

(EXPR)                    
--------------------------

2016-03-01 10:11:00.000000

--- 1 row(s) selected.
>>
>>select to_date('2017/05/15 10:11:12', 'yyyy/mm/dd hh24:mi:ss') from dual;

(EXPR)                    
--------------------------

2017-05-15 10:11:12.000000

--- 1 row(s) selected.
>>select to_char(timestamp '2017-05-15 10:11:12', 'yyyy/mm/dd hh24:mi:ss') from dual;

(EXPR)             
-------------------

2017/05/15 10:11:12

--- 1 row(s) selected.
>>
>>select to_time ('10:23:34', 'HH24:MI:SS') from (values(1)) x(a);

(EXPR)  
--------

10:23:34

--- 1 row(s) selected.
>>select to_time ('10:23:34', 'HH:MI:SS') from (values(1)) x(a);

(EXPR)  
--------

10:23:34

--- 1 row(s) selected.
>>
>>select to_char(DATE '2016-03-01', 'YYYY-MM-DD') from (values(1)) x(a);

(EXPR)    
----------

2016-03-01

--- 1 row(s) selected.
>>select to_char(DATE '2016-03-01', 'MM/DD/YYYY') from (values(1)) x(a);

(EXPR)    
----------

03/01/2016

--- 1 row(s) selected.
>>select to_char(DATE '2016-03-01', 'DD.MM.YYYY') from (values(1)) x(a);

(EXPR)    
----------

01.03.2016

--- 1 row(s) selected.
>>select to_char(DATE '2016-03-01', 'YYYY-MM') from (values(1)) x(a);

(EXPR) 
-------

2016-03

--- 1 row(s) selected.
>>select to_char(DATE '2016-03-01', 'YYYY/MM/DD') from (values(1)) x(a);

(EXPR)    
----------

2016/03/01

--- 1 row(s) selected.
>>select to_char(DATE '2016-03-01', 'YYYYMMDD') from (values(1)) x(a);

(EXPR)  
--------

20160301

--- 1 row(s) selected.
>>select to_char(DATE '2016-03-01', 'YY/MM/DD') from (values(1)) x(a);

(EXPR)  
--------

16/03/01

--- 1 row(s) selected.
>>select to_char(DATE '2016-03-01', 'MM/DD/YY') from (values(1)) x(a);

(EXPR)  
--------

03/01/16

--- 1 row(s) selected.
>>select to_char(DATE '2016-03-01', 'MM-DD-YYYY') from (values(1)) x(a);

(EXPR)    
----------

03-01-2016

--- 1 row(s) selected.
>>select to_char(DATE '2016-03-01', 'YYYYMM') from (values(1)) x(a);

(EXPR)
------

201603

--- 1 row(s) selected.
>>select to_char(DATE '2016-03-01', 'DD-MM-YYYY') from (values(1)) x(a);

(EXPR)    
----------

01-03-2016

--- 1 row(s) selected.
>>select to_char(DATE '2016-03-01', 'DD-MON-YYYY') from (values(1)) x(a);

(EXPR)     
-----------

01-MAR-2016

--- 1 row(s) selected.
>>select to_char(DATE '2016-03-01', 'DDMONYYYY') from (values(1)) x(a);

(EXPR)   
---------

01MAR2016

--- 1 row(s) selected.
>>select to_char(DATE '2016-03-01', 'YYYYMMDDHH24MISS') from (values(1)) x(a);

(EXPR)        
--------------

20160301000000

--- 1 row(s) selected.
>>select to_char(DATE '2016-03-01', 'DD.MM.YYYY HH24.MI.SS') from (values(1)) x(a);

(EXPR)             
-------------------

01.03.2016 00:00:00

--- 1 row(s) selected.
>>select to_char(DATE '2016-03-01', 'YYYY-MM-DD HH24:MI:SS') from (values(1)) x(a);

(EXPR)             
-------------------

2016-03-01 00:00:00

--- 1 row(s) selected.
>>select to_char(DATE '2016-03-01', 'MM/DD/YYYY HH24:MI:SS') from (values(1)) x(a);

(EXPR)             
-------------------

03/01/2016 00:00:00

--- 1 row(s) selected.
>>select to_char(DATE '2016-03-01', 'DD-MON-YYYY HH:MI:SS') from (values(1)) x(a);

(EXPR)              
--------------------

01-MAR-2016 00:00:00

--- 1 row(s) selected.
>>select to_char(TIMESTAMP '2016-03-01 10:11:12', 'YYYYMMDDHH24MISS') from (values(1)) x(a);

(EXPR)        
--------------

20160301101112

--- 1 row(s) selected.
>>select to_char(TIMESTAMP '2016-03-01 10:11:12', 'DD.MM.YYYY HH24.MI.SS') from (values(1)) x(a);

(EXPR)             
-------------------

01.03.2016 10.11.12

--- 1 row(s) selected.
>>select to_char(TIMESTAMP '2016-03-01 10:11:12', 'YYYY-MM-DD HH24:MI:SS') from (values(1)) x(a);

(EXPR)             
-------------------

2016-03-01 10:11:12

--- 1 row(s) selected.
>>select to_char(TIMESTAMP '2016-03-01 10:11:12', 'MM/DD/YYYY HH24:MI:SS') from (values(1)) x(a);

(EXPR)             
-------------------

03/01/2016 10:11:12

--- 1 row(s) selected.
>>select to_char(TIMESTAMP '2016-03-01 10:11:12', 'DD-MON-YYYY HH:MI:SS') from (values(1)) x(a);

(EXPR)              
--------------------

01-MAR-2016 10:11:12

--- 1 row(s) selected.
>>select to_char(TIMESTAMP '2016-03-01 10:11:12', 'MONTH DD, YYYY, HH:MI') from (values(1)) x(a);

(EXPR)                   
-------------------------

March 01, 2016, 10:11    

--- 1 row(s) selected.
>>select to_char (time '10:23:34', 'HH24:MI:SS') from (values(1)) x(a);

(EXPR)  
--------

10:23:34

--- 1 row(s) selected.
>>select to_char (time '10:23:34', 'HH:MI:SS') from (values(1)) x(a);

(EXPR)  
--------

10:23:34

--- 1 row(s) selected.
>>
>>select to_char(TIMESTAMP '2016-03-01 10:11:12', 'YYYY-MM-DD') from (values(1)) x(a);

(EXPR)    
----------

2016-03-01

--- 1 row(s) selected.
>>select to_char(TIMESTAMP '2016-03-01 10:11:12', 'HH:MI:SS') from (values(1)) x(a);

(EXPR)  
--------

10:11:12

--- 1 row(s) selected.
>>
>>select dateformat(time '10:11:12.1', default) from (values(1)) x(a);

(EXPR)    
----------

10:11:12.1

--- 1 row(s) selected.
>>select dateformat(time '10:11:12.123', usa) from (values(1)) x(a);

(EXPR)         
---------------

10:11:12.123 AM

--- 1 row(s) selected.
>>select dateformat(time '10:11:12', european) from (values(1)) x(a);

(EXPR)  
--------

10.11.12

--- 1 row(s) selected.
>>select dateformat(time '10:11:12.12', usa) from (values(1)) x(a);

(EXPR)        
--------------

10:11:12.12 AM

--- 1 row(s) selected.
>>select dateformat(timestamp '2016-03-01 10:11:12.1', default) from (values(1)) x(a);

(EXPR)               
---------------------

2016-03-01 10:11:12.1

--- 1 row(s) selected.
>>select dateformat(timestamp '2016-03-01 10:11:12', usa) from (values(1)) x(a);

(EXPR)             
-------------------

03/01/2016 10:11:12

--- 1 row(s) selected.
>>select dateformat(timestamp '2016-03-01 10:11:12.123', european) from (values(1)) x(a);

(EXPR)                 
-----------------------

01.03.2016 10.11.12.123

--- 1 row(s) selected.
>>select dateformat(timestamp '2016-03-01 10:11:12.12', usa) from (values(1)) x(a);

(EXPR)                   
-------------------------

03/01/2016 10:11:12.12 AM

--- 1 row(s) selected.
>>select dateformat(timestamp '2016-03-01 20:11:12.12', usa) from (values(1)) x(a);

(EXPR)                   
-------------------------

03/01/2016 08:11:12.12 PM

--- 1 row(s) selected.
>>select cast(DATEFORMAT (TIMESTAMP '1990-06-11 07:00:09.00', USA) as char(30)) from (values (1)) x(a);

(EXPR)                        
------------------------------

06/11/1990 07:00:09.00 AM     

--- 1 row(s) selected.
>>
>>select YEAR(interval '10-05' year to month) from (values(1)) as t(a);

(EXPR)
------

    10

--- 1 row(s) selected.
>>select MONTH(interval '02-05' year to month) from (values(1)) as t(a);

(EXPR)
------

     5

--- 1 row(s) selected.
>>select DAY(interval '5' day) from (values(1)) as t(a);

(EXPR)
------

     5

--- 1 row(s) selected.
>>select HOUR(interval '5:2:15:36.33' day to second(2)) from (values(1)) as t(a);

(EXPR)
------

     2

--- 1 row(s) selected.
>>select MINUTE(interval '5:13:25:2.12' day to second(2)) from (values(1)) as t(a);

(EXPR)
------

    25

--- 1 row(s) selected.
>>select extract (year from INTERVAL '97-02' YEAR TO MONTH) from (values (1)) as t(a);

(EXPR)
------

    97

--- 1 row(s) selected.
>>select interval '8' year / 4 from dual;

(EXPR)
------

     2

--- 1 row(s) selected.
>>
>>drop table if exists t030t1;

--- SQL operation complete.
>>create table t030t1 (a date, b char(30), c varchar(30), d timestamp);

--- SQL operation complete.
>>insert into t030t1 values (date '2016-03-01', '2016-03-01', '2016-03-01', 
+>           timestamp '2017-01-13 13:13:13');

--- 1 row(s) inserted.
>>
>>select to_char(a, 'YYYYMMDD') from t030t1;

(EXPR)  
--------

20160301

--- 1 row(s) selected.
>>select a (date, format 'YYYYMMDD') from t030t1;

(EXPR)  
--------

20160301

--- 1 row(s) selected.
>>select to_date(b, 'YYYY-MM-DD') from t030t1;

(EXPR)    
----------

2016-03-01

--- 1 row(s) selected.
>>select to_date(c, 'YYYY-MM-DD') from t030t1;

(EXPR)    
----------

2016-03-01

--- 1 row(s) selected.
>>select * from t030t1 where to_date(c, 'YYYY-MM-DD') = DATE '2016-03-01';

A           B                               C                               D
----------  ------------------------------  ------------------------------  --------------------------

2016-03-01  2016-03-01                      2016-03-01                      2017-01-13 13:13:13.000000

--- 1 row(s) selected.
>>
>>select case when timestamp '2017-01-13 13:13:13' > date '2017-01-13' then 'pass' else 'fail' end from dual;

(EXPR)
------

pass  

--- 1 row(s) selected.
>>select case when timestamp '2017-01-13 13:13:13' > date '2017-01-14' then 'pass' else 'fail' end from dual;

(EXPR)
------

fail  

--- 1 row(s) selected.
>>select * from t030t1 where d > date '2017-01-13';

A           B                               C                               D
----------  ------------------------------  ------------------------------  --------------------------

2016-03-01  2016-03-01                      2016-03-01                      2017-01-13 13:13:13.000000

--- 1 row(s) selected.
>>select * from t030t1 where d > date '2017-01-14';

--- 0 row(s) selected.
>>
>>-- negative tests
>>select to_date('2016-03-01', 'YYYYMM-DD') from (values(1)) x(a);

*** ERROR[4065] The format, "YYYYMM-DD", specified in the TO_DATE function is not supported.

*** ERROR[8822] The statement was not prepared.

>>select to_date(DATE '2016-03-01', 'YYYY-MM-DD') from (values(1)) x(a);

*** ERROR[4043] The operand of function TO_DATE must be character.

*** ERROR[8822] The statement was not prepared.

>>select to_date('0103.2016', 'DD.MM.YYYY') from (values(1)) x(a);

*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted. Source data: 0103.2016

--- 0 row(s) selected.
>>select to_char('2016-03-01', 'YYYY-MM-DD') from (values(1)) x(a);

*** ERROR[4071] The first operand of function TO_CHAR must be a datetime.

*** ERROR[8822] The statement was not prepared.

>>select to_date(20160301, 'YYYYMMDD') from (values(1)) x(a);

*** ERROR[4043] The operand of function TO_DATE must be character.

*** ERROR[8822] The statement was not prepared.

>>select to_char(date '2016-03-01', 'HH:MI:SS') from (values(1)) x(a);

*** ERROR[4072] The operand of function TO_CHAR must be a datetime containing a time.

*** ERROR[8822] The statement was not prepared.

>>select to_char(time '10:23:34', 'YYYY-MM-DD') from (values(1)) x(a);

*** ERROR[4072] The operand of function TO_CHAR must be a datetime containing a date.

*** ERROR[8822] The statement was not prepared.

>>select to_char(time '10:23:34', 'DD.MM.YYYY HH24.MI.SS') from (values(1)) x(a);

*** ERROR[4072] The operand of function TO_CHAR must be a datetime containing a date.

*** ERROR[8822] The statement was not prepared.

>>select to_date ('10:23:34', 'HH24:MI:SS') from (values(1)) x(a);

*** ERROR[4065] The format, "HH24:MI:SS", specified in the TO_DATE function is not supported.

*** ERROR[8822] The statement was not prepared.

>>select to_date ('10:23:34', 'HH:MI:SS') from (values(1)) x(a);

*** ERROR[4065] The format, "HH24:MI:SS", specified in the TO_DATE function is not supported.

*** ERROR[8822] The statement was not prepared.

>>select to_date('04-DEC-2016','DDMONYYYY') from (values(1)) x(a);

*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted. Source data: -DEC-2016

--- 0 row(s) selected.
>>select to_time('2016-03-01', 'YYYY-MM-DD') from (values(1)) x(a);

*** ERROR[4065] The format, "YYYY-MM-DD", specified in the TO_TIME function is not supported.

*** ERROR[8822] The statement was not prepared.

>>select to_time('03/01/2016 10:11:12', 'MM/DD/YYYY HH24:MI:SS') from (values(1)) x(a);

*** ERROR[4065] The format, "MM/DD/YYYY HH24:MI:SS", specified in the TO_TIME function is not supported.

*** ERROR[8822] The statement was not prepared.

>>select to_time('01:61:01', 'HH24:MI:SS') from (values(1)) x(a);

*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted. Source data: 01:61:01

--- 0 row(s) selected.
>>select to_date('2016-04-33 01:01:01','YYYY-MM-DD HH24:MI:SS') from (values(1)) x(a);

*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted. Source data: 2016-04-33 01:01:01

--- 0 row(s) selected.
>>select * from t030t1 where to_date(c, 'YYYY-MM-DD') = '2016-03-01';

A           B                               C                               D
----------  ------------------------------  ------------------------------  --------------------------

2016-03-01  2016-03-01                      2016-03-01                      2017-01-13 13:13:13.000000

--- 1 row(s) selected.
>>select to_date('01.03.2016:10:11:12', 'DD.MM.YYYY:HH24:MI:SS') from (values(1)) x(a);

*** ERROR[4065] The format, "DD.MM.YYYY:HH24:MI:SS", specified in the TO_DATE function is not supported.

*** ERROR[8822] The statement was not prepared.

>>select to_date('01.03.2016:10:11:12', 'DD.MM.YYYY HH24:MI:SS') from (values(1)) x(a);

*** ERROR[4065] The format, "DD.MM.YYYY HH24:MI:SS", specified in the TO_DATE function is not supported.

*** ERROR[8822] The statement was not prepared.

>>select to_char(3, 'HH:MI:SS') from (values(1)) x(a);

*** ERROR[4071] The first operand of function TO_CHAR must be a datetime.

*** ERROR[8822] The statement was not prepared.

>>select dateformat(3, usa) from (values(1)) x(a);

*** ERROR[4071] The first operand of function DATEFORMAT must be a datetime.

*** ERROR[8822] The statement was not prepared.

>>
>>-- some formats only enabled in special mode. Not externalized.
>>cqd mode_special_4 'ON';

--- SQL operation complete.
>>select to_date(20160301, 'YYYYMMDD') from (values(1)) x(a);

(EXPR)    
----------

2016-03-01

--- 1 row(s) selected.
>>select to_date(12345678, '99:99:99:99') from (values(1)) x(a);

(EXPR)     
-----------

12:34:56:78

--- 1 row(s) selected.
>>select to_date(-12345678, '-99:99:99:99') from (values(1)) x(a);

(EXPR)      
------------

-12:34:56:78

--- 1 row(s) selected.
>>
>>-- ms4 error cases
>>select to_date(123456789, '99:99:99:99') from (values(1)) x(a);

*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted. Source data: 1

*** ERROR[8001] An internal executor error occurred.

--- 0 row(s) selected.
>>select to_date(-12345678, '99:99:99:99') from (values(1)) x(a);

*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted. Source data: -12345678

*** ERROR[8001] An internal executor error occurred.

--- 0 row(s) selected.
>>select to_date(1e0, '99:99:99:99') from (values(1)) x(a);

*** ERROR[4046] The operands of function TO_DATE must be exact numeric.

*** ERROR[8822] The statement was not prepared.

>>select to_date(1.2, '99:99:99:99') from (values(1)) x(a);

*** ERROR[4047] The operands of function TO_DATE must have a scale of 0.

*** ERROR[8822] The statement was not prepared.

>>
>>-- cannot compare timestamp to time, or date to time
>>select case when timestamp '2017-01-17 10:10:10' > time '10:10:10' then 'pass' else 'fail' end from dual;

*** ERROR[4041] Type TIMESTAMP(0) cannot be compared with type TIME(0).

*** ERROR[8822] The statement was not prepared.

>>select case when date '2017-01-17' > time '10:10:10' then 'pass' else 'fail' end from dual;

*** ERROR[4041] Type DATE cannot be compared with type TIME(0).

*** ERROR[8822] The statement was not prepared.

>>
>>log;
