| >> |
| >>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_time(1, 'HH:MI:SS') from dual; |
| |
| *** ERROR[3415] The first operand of function TO_TIME is not valid. It must be a datetime datatype containing the time field or a character datatype. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >>select to_time(date '2018-04-02', 'HH:MI:SS') from dual; |
| |
| *** ERROR[3415] The first operand of function TO_TIME is not valid. It must be a datetime datatype containing the time field or a character datatype. |
| |
| *** ERROR[8822] The statement was not prepared. |
| |
| >> |
| >>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; |
