core/sql/regress/seabase/TEST030

-- @@@ START COPYRIGHT @@@
--
-- Licensed to the Apache Software Foundation (ASF) under one
-- or more contributor license agreements.  See the NOTICE file
-- distributed with this work for additional information
-- regarding copyright ownership.  The ASF licenses this file
-- to you under the Apache License, Version 2.0 (the
-- "License"); you may not use this file except in compliance
-- with the License.  You may obtain a copy of the License at
--
--   http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing,
-- software distributed under the License is distributed on an
-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-- KIND, either express or implied.  See the License for the
-- specific language governing permissions and limitations
-- under the License.
--
-- @@@ END COPYRIGHT @@@
--

-- tests for TO_DATE, TO_CHAR, TO_TIMESTAMP, TO_NUMBER functions

log LOG030 clear;

select to_date('2016-03-01', 'YYYY-MM-DD') from (values(1)) x(a);
select to_date('03/01/2016', 'mm/dd/yyyy') from (values(1)) x(a);
select to_date('01.03.2016', 'DD.MM.YYYY') from (values(1)) x(a);
select to_date('2016-03', 'YYYY-MM') from (values(1)) x(a);
select to_date('2016/03/01', 'YYYY/MM/DD') from (values(1)) x(a);
select to_date('20160301', 'YYYYMMDD') from (values(1)) x(a);
select to_date('16/03/01', 'YY/MM/DD') from (values(1)) x(a);
select to_date('03/01/16', 'MM/DD/YY') from (values(1)) x(a);
select to_date('03-01-2016', 'MM-DD-YYYY') from (values(1)) x(a);
select to_date('201603', 'YYYYmm') from (values(1)) x(a);
select to_date('01-03-2016', 'DD-MM-YYYY') from (values(1)) x(a);
select to_date('01-MAR-2016', 'DD-MON-YYYY') from (values(1)) x(a);
select to_date('01MAR2016', 'DDMONYYYY') from (values(1)) x(a);
select to_date('20160301101112', 'YYYYMMDDHH24MISS') from (values(1)) x(a);
select to_date('01.03.2016 10.11.12', 'DD.MM.YYYY HH24.MI.SS') from (values(1)) x(a);
select to_date('2016-03-01 10:11:12', 'YYYY-MM-DD HH24:MI:SS') from (values(1)) x(a);
select to_date('03/01/2016 10:11:12', 'MM/DD/YYYY HH24:MI:SS') from (values(1)) x(a);
select to_date('01-MAR-2016 10:11:12', 'DD-MON-YYYY HH:MI:SS') from (values(1)) x(a);
select to_date('March 01, 2016, 10:11', 'MONTH DD, YYYY, HH:MI') from (values(1)) x(a);

select to_date('2017/05/15 10:11:12', 'yyyy/mm/dd hh24:mi:ss') from dual;
select to_char(timestamp '2017-05-15 10:11:12', 'yyyy/mm/dd hh24:mi:ss') from dual;

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

select to_char(DATE '2016-03-01', 'YYYY-MM-DD') from (values(1)) x(a);
select to_char(DATE '2016-03-01', 'MM/DD/YYYY') from (values(1)) x(a);
select to_char(DATE '2016-03-01', 'DD.MM.YYYY') from (values(1)) x(a);
select to_char(DATE '2016-03-01', 'YYYY-MM') from (values(1)) x(a);
select to_char(DATE '2016-03-01', 'YYYY/MM/DD') from (values(1)) x(a);
select to_char(DATE '2016-03-01', 'YYYYMMDD') from (values(1)) x(a);
select to_char(DATE '2016-03-01', 'YY/MM/DD') from (values(1)) x(a);
select to_char(DATE '2016-03-01', 'MM/DD/YY') from (values(1)) x(a);
select to_char(DATE '2016-03-01', 'MM-DD-YYYY') from (values(1)) x(a);
select to_char(DATE '2016-03-01', 'YYYYMM') from (values(1)) x(a);
select to_char(DATE '2016-03-01', 'DD-MM-YYYY') from (values(1)) x(a);
select to_char(DATE '2016-03-01', 'DD-MON-YYYY') from (values(1)) x(a);
select to_char(DATE '2016-03-01', 'DDMONYYYY') from (values(1)) x(a);
select to_char(DATE '2016-03-01', 'YYYYMMDDHH24MISS') from (values(1)) x(a);
select to_char(DATE '2016-03-01', 'DD.MM.YYYY HH24.MI.SS') from (values(1)) x(a);
select to_char(DATE '2016-03-01', 'YYYY-MM-DD HH24:MI:SS') from (values(1)) x(a);
select to_char(DATE '2016-03-01', 'MM/DD/YYYY HH24:MI:SS') from (values(1)) x(a);
select to_char(DATE '2016-03-01', 'DD-MON-YYYY HH:MI:SS') from (values(1)) x(a);
select to_char(TIMESTAMP '2016-03-01 10:11:12', 'YYYYMMDDHH24MISS') from (values(1)) x(a);
select to_char(TIMESTAMP '2016-03-01 10:11:12', 'DD.MM.YYYY HH24.MI.SS') from (values(1)) x(a);
select to_char(TIMESTAMP '2016-03-01 10:11:12', 'YYYY-MM-DD HH24:MI:SS') from (values(1)) x(a);
select to_char(TIMESTAMP '2016-03-01 10:11:12', 'MM/DD/YYYY HH24:MI:SS') from (values(1)) x(a);
select to_char(TIMESTAMP '2016-03-01 10:11:12', 'DD-MON-YYYY HH:MI:SS') from (values(1)) x(a);
select to_char(TIMESTAMP '2016-03-01 10:11:12', 'MONTH DD, YYYY, HH:MI') from (values(1)) x(a);
select to_char (time '10:23:34', 'HH24:MI:SS') from (values(1)) x(a);
select to_char (time '10:23:34', 'HH:MI:SS') from (values(1)) x(a);

select to_char(TIMESTAMP '2016-03-01 10:11:12', 'YYYY-MM-DD') from (values(1)) x(a);
select to_char(TIMESTAMP '2016-03-01 10:11:12', 'HH:MI:SS') from (values(1)) x(a);

select dateformat(time '10:11:12.1', default) from (values(1)) x(a);
select dateformat(time '10:11:12.123', usa) from (values(1)) x(a);
select dateformat(time '10:11:12', european) from (values(1)) x(a);
select dateformat(time '10:11:12.12', usa) from (values(1)) x(a);
select dateformat(timestamp '2016-03-01 10:11:12.1', default) from (values(1)) x(a);
select dateformat(timestamp '2016-03-01 10:11:12', usa) from (values(1)) x(a);
select dateformat(timestamp '2016-03-01 10:11:12.123', european) from (values(1)) x(a);
select dateformat(timestamp '2016-03-01 10:11:12.12', usa) from (values(1)) x(a);
select dateformat(timestamp '2016-03-01 20:11:12.12', usa) from (values(1)) x(a);
select cast(DATEFORMAT (TIMESTAMP '1990-06-11 07:00:09.00', USA) as char(30)) from (values (1)) x(a);

select YEAR(interval '10-05' year to month) from (values(1)) as t(a);
select MONTH(interval '02-05' year to month) from (values(1)) as t(a);
select DAY(interval '5' day) from (values(1)) as t(a);
select HOUR(interval '5:2:15:36.33' day to second(2)) from (values(1)) as t(a);
select MINUTE(interval '5:13:25:2.12' day to second(2)) from (values(1)) as t(a);
select extract (year from INTERVAL '97-02' YEAR TO MONTH) from (values (1)) as t(a);
select interval '8' year / 4 from dual;

drop table if exists t030t1;
create table t030t1 (a date, b char(30), c varchar(30), d timestamp);
insert into t030t1 values (date '2016-03-01', '2016-03-01', '2016-03-01', 
           timestamp '2017-01-13 13:13:13');

select to_char(a, 'YYYYMMDD') from t030t1;
select a (date, format 'YYYYMMDD') from t030t1;
select to_date(b, 'YYYY-MM-DD') from t030t1;
select to_date(c, 'YYYY-MM-DD') from t030t1;
select * from t030t1 where to_date(c, 'YYYY-MM-DD') = DATE '2016-03-01';

select case when timestamp '2017-01-13 13:13:13' > date '2017-01-13' then 'pass' else 'fail' end from dual;
select case when timestamp '2017-01-13 13:13:13' > date '2017-01-14' then 'pass' else 'fail' end from dual;
select * from t030t1 where d > date '2017-01-13';
select * from t030t1 where d > date '2017-01-14';

-- negative tests
select to_date('2016-03-01', 'YYYYMM-DD') from (values(1)) x(a);
select to_date(DATE '2016-03-01', 'YYYY-MM-DD') from (values(1)) x(a);
select to_date('0103.2016', 'DD.MM.YYYY') from (values(1)) x(a);
select to_char('2016-03-01', 'YYYY-MM-DD') from (values(1)) x(a);
select to_date(20160301, 'YYYYMMDD') from (values(1)) x(a);
select to_char(date '2016-03-01', 'HH:MI:SS') from (values(1)) x(a);
select to_char(time '10:23:34', 'YYYY-MM-DD') from (values(1)) x(a);
select to_char(time '10:23:34', 'DD.MM.YYYY HH24.MI.SS') from (values(1)) x(a);
select to_date ('10:23:34', 'HH24:MI:SS') from (values(1)) x(a);
select to_date ('10:23:34', 'HH:MI:SS') from (values(1)) x(a);
select to_date('04-DEC-2016','DDMONYYYY') from (values(1)) x(a);

select to_time('2016-03-01', 'YYYY-MM-DD') from (values(1)) x(a);
select to_time('03/01/2016 10:11:12', 'MM/DD/YYYY HH24:MI:SS') from (values(1)) x(a);
select to_time('01:61:01', 'HH24:MI:SS') from (values(1)) x(a);
select to_time(1, 'HH:MI:SS') from dual;
select to_time(date '2018-04-02', 'HH:MI:SS') from dual;

select to_date('2016-04-33 01:01:01','YYYY-MM-DD HH24:MI:SS') from (values(1)) x(a);
select * from t030t1 where to_date(c, 'YYYY-MM-DD') = '2016-03-01';
select to_date('01.03.2016:10:11:12', 'DD.MM.YYYY:HH24:MI:SS') from (values(1)) x(a);
select to_date('01.03.2016:10:11:12', 'DD.MM.YYYY HH24:MI:SS') from (values(1)) x(a);
select to_char(3, 'HH:MI:SS') from (values(1)) x(a);
select dateformat(3, usa) from (values(1)) x(a);

-- some formats only enabled in special mode. Not externalized.
cqd mode_special_4 'ON';
select to_date(20160301, 'YYYYMMDD') from (values(1)) x(a);
select to_date(12345678, '99:99:99:99') from (values(1)) x(a);
select to_date(-12345678, '-99:99:99:99') from (values(1)) x(a);

-- ms4 error cases
select to_date(123456789, '99:99:99:99') from (values(1)) x(a);
select to_date(-12345678, '99:99:99:99') from (values(1)) x(a);
select to_date(1e0, '99:99:99:99') from (values(1)) x(a);
select to_date(1.2, '99:99:99:99') from (values(1)) x(a);

-- 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;
select case when date '2017-01-17' > time '10:10:10' then 'pass' else 'fail' end from dual;

log;