ij> -- -- 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. -- -- -- Test the builtin date/time types -- assumes these builtin types exist: -- int, smallint, char, varchar, real -- -- other things we might test: -- interaction with UUID and other user defined types -- compatibility with dynamic parameters and JDBC getDate etc. methods -- -- Test the arithmetic operators -- create table t (i int, s smallint, c char(10), v varchar(50), d double precision, r real, e date, t time, p timestamp); 0 rows inserted/updated/deleted ij> insert into t values (null, null, null, null, null, null, null, null, null); 1 row inserted/updated/deleted ij> insert into t values (0, 100, 'hello', 'everyone is here', 200.0e0, 300.0e0, date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx)); 1 row inserted/updated/deleted ij> insert into t values (-1, -100, 'goodbye', 'everyone is there', -200.0e0, -300.0e0, date('1992-01-01'), time('12:30:30'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx)); 1 row inserted/updated/deleted ij> -- date/times don't support math, show each combination select e + e from t; ERROR 42Y95: The '+' operator with a left operand type of 'DATE' and a right operand type of 'DATE' is not supported. ij> select i + e from t; ERROR 42Y95: The '+' operator with a left operand type of 'INTEGER' and a right operand type of 'DATE' is not supported. ij> select p / p from t; ERROR 42Y95: The '/' operator with a left operand type of 'TIMESTAMP' and a right operand type of 'TIMESTAMP' is not supported. ij> select p * s from t; ERROR 42Y95: The '*' operator with a left operand type of 'TIMESTAMP' and a right operand type of 'SMALLINT' is not supported. ij> select t - t from t; ERROR 42Y95: The '-' operator with a left operand type of 'TIME' and a right operand type of 'TIME' is not supported. ij> select -t from t; ERROR 42X37: The unary '-' operator is not allowed on the 'TIME' type. ij> select +e from t; ERROR 42X37: The unary '+' operator is not allowed on the 'DATE' type. ij> -- -- comparisons -- -- select each one in turn -- each pair gets the same result select e from t where e = date('1992-01-01'); E ---------- 1992-01-01 1992-01-01 ij> select e from t where date('1992-01-01') = e; E ---------- 1992-01-01 1992-01-01 ij> select t from t where t > time('09:30:15'); T -------- 12:30:30 12:30:30 ij> select t from t where time('09:30:15') < t; T -------- 12:30:30 12:30:30 ij> select p from t where p < timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx); P -------------------------- xxxxxxFILTERED-TIMESTAMPxxxxx xxxxxxFILTERED-TIMESTAMPxxxxx ij> select p from t where timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx)> p; P -------------------------- xxxxxxFILTERED-TIMESTAMPxxxxx xxxxxxFILTERED-TIMESTAMPxxxxx ij> -- now look for a value that isn't in the table select e from t where e <> date('1992-01-01'); E ---------- ij> select e from t where date('1992-01-01') <> e; E ---------- ij> -- now test null = null semantics select e, t, p from t where e = e or t = t or p = p; E |T |P ---------------------------------------------- 1992-01-01|12:30:30|xxxxxxFILTERED-TIMESTAMPxxxxx 1992-01-01|12:30:30|xxxxxxFILTERED-TIMESTAMPxxxxx ij> -- now test <=, >=, <> -- each pair gets the same result select e from t where e >= date('1990-01-01'); E ---------- 1992-01-01 1992-01-01 ij> select e from t where date('1990-01-01')<= e; E ---------- 1992-01-01 1992-01-01 ij> select t from t where t <= time('09:30:15'); T -------- ij> select t from t where time('09:30:15') >= t; T -------- ij> select p from t where p <> timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx); P -------------------------- xxxxxxFILTERED-TIMESTAMPxxxxx xxxxxxFILTERED-TIMESTAMPxxxxx ij> select p from t where timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx)<> p; P -------------------------- xxxxxxFILTERED-TIMESTAMPxxxxx xxxxxxFILTERED-TIMESTAMPxxxxx ij> -- show comparisons with mixed types don't work select e from t where e <= i; ERROR 42818: Comparisons between 'DATE' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. select tablename from sys.systables where CAST(tablename as VARCHAR(128)) = 'T1') ij> select e from t where t < s; ERROR 42818: Comparisons between 'TIME' and 'SMALLINT' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. select tablename from sys.systables where CAST(tablename as VARCHAR(128)) = 'T1') ij> select e from t where p > d; ERROR 42818: Comparisons between 'TIMESTAMP' and 'DOUBLE' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. select tablename from sys.systables where CAST(tablename as VARCHAR(128)) = 'T1') ij> select e from t where e >= t; ERROR 42818: Comparisons between 'DATE' and 'TIME' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. select tablename from sys.systables where CAST(tablename as VARCHAR(128)) = 'T1') ij> select e from t where t <> p; ERROR 42818: Comparisons between 'TIME' and 'TIMESTAMP' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. select tablename from sys.systables where CAST(tablename as VARCHAR(128)) = 'T1') ij> select e from t where p = e; ERROR 42818: Comparisons between 'TIMESTAMP' and 'DATE' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. select tablename from sys.systables where CAST(tablename as VARCHAR(128)) = 'T1') ij> -- check limit values values( date('0001-1-1'), date('9999-12-31'), date('2/29/2000'), date('29.2.2004')); 1 |2 |3 |4 ------------------------------------------- 0001-01-01|9999-12-31|2000-02-29|2004-02-29 ij> values( time('00:00:00'), time('23:59:59')); 1 |2 ----------------- 00:00:00|23:59:59 ij> values( time('00 AM'), time( '12:59 AM'), time('1 PM'), time('12:59 PM')); 1 |2 |3 |4 ----------------------------------- 00:00:00|00:59:00|13:00:00|12:59:00 ij> values( time('00.00.00'), time('23.59.59'), time('24.00.00')); 1 |2 |3 -------------------------- 00:00:00|23:59:59|00:00:00 ij> values( timestamp('0001-1-1 00:00:00'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx')); 1 |2 ----------------------------------------------------- xxxxxxFILTERED-TIMESTAMPxxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx ij> -- show that overflow and underflow are not allowed -- (SQL92 would have these report errors) values( date('0000-01-01')); ERROR 22008: '0000-01-01' is an invalid argument to the date function. ij> values( date('2000-00-01')); ERROR 22008: '2000-00-01' is an invalid argument to the date function. ij> values( date('2000-01-00')); ERROR 22008: '2000-01-00' is an invalid argument to the date function. ij> values( date('10000-01-01')); ERROR 22008: '10000-01-01' is an invalid argument to the date function. ij> values( date('2000-13-01')); ERROR 22008: '2000-13-01' is an invalid argument to the date function. ij> values( date('2000-01-32')); ERROR 22008: '2000-01-32' is an invalid argument to the date function. ij> values( date('1900-02-29')); ERROR 22008: '1900-02-29' is an invalid argument to the date function. ij> values( date('2001-02-29')); ERROR 22008: '2001-02-29' is an invalid argument to the date function. ij> values( time('25.00.00')); ERROR 22007: The string representation of a datetime value is out of range. ij> values( time('24.00.01')); ERROR 22007: The string representation of a datetime value is out of range. ij> values( time('0:60:00')); ERROR 22007: The string representation of a datetime value is out of range. ij> values( time('00:00:60')); ERROR 22007: The string representation of a datetime value is out of range. ij> -- show garbage in == errors out select date( 'xxxx') from t where p is null; ERROR 22008: 'xxxx' is an invalid argument to the date function. ij> select time( '') from t where p is null; ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> select timestamp( 'is there anything here?' )from t where p is null; ERROR 22008: 'is there anything here?' is an invalid argument to the timestamp function. ij> select timestamp( '1992-01- there anything here?' )from t where p is null; ERROR 22008: '1992-01- there anything here?' is an invalid argument to the timestamp function. ij> select timestamp( '--::' )from t where p is null; ERROR 22008: '--::' is an invalid argument to the timestamp function. ij> select time('::::') from t where p is null; ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> -- show is not null at work select * from t where e is not null and t is not null and p is not null; I |S |C |V |D |R |E |T |P -------------------------------------------------------------------------------------------------------------------------------------------------------------------- 0 |100 |hello |everyone is here |200.0 |300.0 |1992-01-01|12:30:30|xxxxxxFILTERED-TIMESTAMPxxxxx -1 |-100 |goodbye |everyone is there |-200.0 |-300.0 |1992-01-01|12:30:30|xxxxxxFILTERED-TIMESTAMPxxxxx ij> -- test =SQ -- this gets cardinality error select 'fail' from t where e = (select e from t); ERROR 21000: Scalar subquery is only allowed to return a single row. ij> -- this works select 'pass' from t where e = (select e from t where d=200); 1 ---- pass pass ij> -- this gets cardinality error select 'fail' from t where t = (select t from t); ERROR 21000: Scalar subquery is only allowed to return a single row. ij> -- this works select 'pass' from t where t = (select t from t where d=200); 1 ---- pass pass ij> -- this gets cardinality error select 'fail' from t where p = (select p from t); ERROR 21000: Scalar subquery is only allowed to return a single row. ij> -- this works select 'pass' from t where p = (select p from t where d=200); 1 ---- pass ij> drop table t; 0 rows inserted/updated/deleted ij> -- -- test syntax: precision cannot be specified -- create table wrong (t time(-100)); ERROR 42X01: Syntax error: Encountered "(" at line 4, column 27. ij> create table wrong (t time(0)); ERROR 42X01: Syntax error: Encountered "(" at line 1, column 27. ij> create table wrong (t time(23)); ERROR 42X01: Syntax error: Encountered "(" at line 1, column 27. ij> create table wrong (t timestamp(-100)); ERROR 42X01: Syntax error: Encountered "(" at line 1, column 32. ij> create table wrong (t timestamp(0)); ERROR 42X01: Syntax error: Encountered "(" at line 1, column 32. ij> create table wrong (t timestamp(6)); ERROR 42X01: Syntax error: Encountered "(" at line 1, column 32. ij> create table wrong (t timestamp(9)); ERROR 42X01: Syntax error: Encountered "(" at line 1, column 32. ij> create table wrong (t timestamp(23)); ERROR 42X01: Syntax error: Encountered "(" at line 1, column 32. ij> -- -- test a variety of inserts and updates -- create table source (i int, s smallint, c char(10), v varchar(50), d double precision, r real, e date, t time, p timestamp); 0 rows inserted/updated/deleted ij> create table target (e date not null, t time not null, p timestamp not null); 0 rows inserted/updated/deleted ij> -- we have already tested inserting literals. insert into source values (1, 2, '3', '4', 5, 6, date('1997-07-07'), time('08:08:08'),timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx)); 1 row inserted/updated/deleted ij> -- these work: insert into target select e,t,p from source; 1 row inserted/updated/deleted ij> -- these will all fail: insert into target select p,e,t from source; ERROR 42821: Columns of type 'DATE' cannot hold values of type 'TIMESTAMP'. ij> insert into target select i,s,d from source; ERROR 42821: Columns of type 'DATE' cannot hold values of type 'INTEGER'. ij> insert into target (t,p) select c,r from source; ERROR 42821: Columns of type 'TIMESTAMP' cannot hold values of type 'REAL'. ij> delete from source; 1 row inserted/updated/deleted ij> insert into source values (null, null, null, null, null, null, null, null, null); 1 row inserted/updated/deleted ij> -- these fail because the target won't take a null -- of any type insert into target values(null, null, null); ERROR 23502: Column 'E' cannot accept a NULL value. ij> insert into target select e,t,p from source; ERROR 23502: Column 'E' cannot accept a NULL value. ij> -- these still fail with type errors: insert into target select p,e,t from source; ERROR 42821: Columns of type 'DATE' cannot hold values of type 'TIMESTAMP'. ij> insert into target select i,s,d from source; ERROR 42821: Columns of type 'DATE' cannot hold values of type 'INTEGER'. ij> insert into target (t,p)select c,r from source; ERROR 42821: Columns of type 'TIMESTAMP' cannot hold values of type 'REAL'. ij> -- expect 1 row in target: select * from target; E |T |P ---------------------------------------------- 1997-07-07|08:08:08|xxxxxxFILTERED-TIMESTAMPxxxxx ij> -- unchanged: update target set e = e, t = t, p = p; 1 row inserted/updated/deleted ij> select * from target; E |T |P ---------------------------------------------- 1997-07-07|08:08:08|xxxxxxFILTERED-TIMESTAMPxxxxx ij> -- alters the row: update target set e = date('1990-01-01'); 1 row inserted/updated/deleted ij> select * from target; E |T |P ---------------------------------------------- 1990-01-01|08:08:08|xxxxxxFILTERED-TIMESTAMPxxxxx ij> -- not settable to null update target set e = null; ERROR 23502: Column 'E' cannot accept a NULL value. ij> select * from target; E |T |P ---------------------------------------------- 1990-01-01|08:08:08|xxxxxxFILTERED-TIMESTAMPxxxxx ij> -- nullable col can be set to null: update source set e = date('1492-10-01'); 1 row inserted/updated/deleted ij> select e from source; E ---------- 1492-10-01 ij> update source set e = null; 1 row inserted/updated/deleted ij> select e from source; E ---------- NULL ij> -- these should get type errors update target set e = 1; ERROR 42821: Columns of type 'DATE' cannot hold values of type 'INTEGER'. ij> update source set p = 1.4e10; ERROR 42821: Columns of type 'TIMESTAMP' cannot hold values of type 'DOUBLE'. ij> update source set i = date('1001-01-01'); ERROR 42821: Columns of type 'INTEGER' cannot hold values of type 'DATE'. ij> -- tests with current functions: delete from source; 1 row inserted/updated/deleted ij> delete from target; 1 row inserted/updated/deleted ij> insert into source values (1, 2, '3', '4', 5, 6, date('1997-06-07'), time('08:08:08'),timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx)); 1 row inserted/updated/deleted ij> -- these tests are 'funny' so that the masters won't show a diff -- every time. select 'pass' from source where current_date = current_date and current_time = current_time and current_timestamp = current_timestamp; 1 ---- pass ij> select 'pass' from source where current_date > date('1996-12-31') and current_time <= time( '23:59:59') -- may oopsie on leap second days and current_timestamp <> timestamp( -- this comment is just more whitespace 'xxxxxxFILTERED-TIMESTAMPxxxxx); 1 ---- pass ij> -- test with DB2 compatible syntax select 'pass' from source where current date = current date and current time = current time and current timestamp = current timestamp; 1 ---- pass ij> select 'pass' from source where current date > date('1996-12-31') and current time <= time( '23:59:59') -- may oopsie on leap second days and current timestamp <> timestamp( -- this comment is just more whitespace 'xxxxxxFILTERED-TIMESTAMPxxxxx); 1 ---- pass ij> -- test escaped functions -- CURRENT_DATE escaped function not supported in DB2 UDB -- CURRENT_TIME escaped function not supported in DB2 UDB select 'pass' from source where current_date = {fn current_date()} and current_time = {fn current_time()} and current_timestamp = current_timestamp; ERROR 42X01: Syntax error: Encountered "current_date" at line 5, column 26. ij> select 'pass' from source where current_date = {fn curdate()} and current_time = {fn curtime()} and current_timestamp = current_timestamp; 1 ---- pass ij> -- current_date() and current_time() not valid in DB2. curdate() and curtime() -- are as escaped functions only. values curdate(); ERROR 42Y03: 'CURDATE' is not recognized as a function or procedure. ij> values curtime(); ERROR 42Y03: 'CURTIME' is not recognized as a function or procedure. ij> values current_date(); ERROR 42X01: Syntax error: Encountered "(" at line 1, column 20. ij> values current_time(); ERROR 42X01: Syntax error: Encountered "(" at line 1, column 20. ij> values {fn current_date()}; ERROR 42X01: Syntax error: Encountered "current_date" at line 1, column 12. ij> values {fn current_time()}; ERROR 42X01: Syntax error: Encountered "current_time" at line 1, column 12. ij> -- DB2 UDB compatible test for escaped functions select 'pass' from source where hour(current_time) = {fn hour(current_time)} and minute(current_time) = {fn minute(current_time)} and second(current_time) = {fn second(current_time)} and year(current_date) = {fn year(current_date)}; 1 ---- pass ij> -- valid jdbc date and time escaped functions values {fn hour('23:38:10')}; 1 ----------- 23 ij> values {fn minute('23:38:10')}; 1 ----------- 38 ij> values {fn second('23:38:10')}; 1 ----------- 10 ij> values {fn year('2004-03-22')}; 1 ----------- 2004 ij> -- currents do have types, these inserts fail: insert into source values (0, 0, '0', '0', 0, 0, current_time, current_time, current_timestamp); ERROR 42821: Columns of type 'DATE' cannot hold values of type 'TIME'. ij> insert into source values (0, 0, '0', '0', 0, 0, current_date, current_timestamp, current_timestamp); ERROR 42821: Columns of type 'TIME' cannot hold values of type 'TIMESTAMP'. ij> insert into source values (0, 0, '0', '0', 0, 0, current_date, current_time, current_date); ERROR 42821: Columns of type 'TIMESTAMP' cannot hold values of type 'DATE'. ij> -- this insert works insert into source values (0, 0, '0', '0', 0, 0, current_date, current_time, current_timestamp); 1 row inserted/updated/deleted ij> -- test with DB2 syntax -- this insert works insert into source values (0, 0, '0', '0', 0, 0, current date, current time, current timestamp); 1 row inserted/updated/deleted ij> -- this test will diff if the select is run just after midnight, -- and the insert above was run just before midnight... select * from source where e <> current_date and p <> current_timestamp; I |S |C |V |D |R |E |T |P -------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 |2 |3 |4 |5.0 |6.0 |1997-06-07|08:08:08|xxxxxxFILTERED-TIMESTAMPxxxxx ij> -- test with DB2 syntax select * from source where e <> current date and p <> current timestamp; I |S |C |V |D |R |E |T |P -------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 |2 |3 |4 |5.0 |6.0 |1997-06-07|08:08:08|xxxxxxFILTERED-TIMESTAMPxxxxx ij> select 'pass' from source where e <= current_date and p <= current_timestamp; 1 ---- pass pass ij> -- reduce it back to one row delete from source where i=0; 2 rows inserted/updated/deleted ij> -- tests with extract: select year( e), month( e), day( date( '1997-01-15')), hour( t), minute( t), second( time( '01:01:42')), year( p), month( p), day( p), hour( timestamp( 'xxxxxxFILTERED-TIMESTAMPxxxxx)), minute( p), second( p) from source; 1 |2 |3 |4 |5 |6 |7 |8 |9 |10 |11 |12 ---------------------------------------------------------------------------------------------------------------------------------------------------------- 1997 |6 |15 |8 |8 |42 |9999 |9 |9 |14 |9 |9.0 ij> -- extract won't work on other types select month( i) from source; ERROR 42X25: The 'EXTRACT MONTH' function is not allowed on the 'INTEGER' type. ij> select hour( d) from source; ERROR 42X25: The 'EXTRACT HOUR' function is not allowed on the 'DOUBLE' type. ij> -- extract won't work on certain field/type combos select month( t) from source; ERROR 42X25: The 'EXTRACT MONTH' function is not allowed on the 'TIME' type. ij> select day( t) from source; ERROR 42X25: The 'EXTRACT DAY' function is not allowed on the 'TIME' type. ij> select year( t) from source; ERROR 42X25: The 'EXTRACT YEAR' function is not allowed on the 'TIME' type. ij> select hour( e) from source; ERROR 42X25: The 'EXTRACT HOUR' function is not allowed on the 'DATE' type. ij> select minute( e) from source; ERROR 42X25: The 'EXTRACT MINUTE' function is not allowed on the 'DATE' type. ij> select second( e) from source; ERROR 42X25: The 'EXTRACT SECOND' function is not allowed on the 'DATE' type. ij> update source set i=month( e), s=minute( t), d=second( p); 1 row inserted/updated/deleted ij> -- should be true and atomics should match field named as label in date/times select i,e as "month",s,t as "minute",d,p as "second" from source where (i = month(e)) and (s = minute(t)) and (d = second(p)); I |month |S |minute |D |second ---------------------------------------------------------------------------------------- 6 |1997-06-07|8 |08:08:08|9.0 |xxxxxxFILTERED-TIMESTAMPxxxxx ij> -- fields should match the fields in the date (in order) select p, year( p) as "year", month( p) as "month", day( p) as "day", hour( p) as "hour", minute( p) as "minute", second( p) as "second" from source; P |year |month |day |hour |minute |second ------------------------------------------------------------------------------------------------------------- xxxxxxFILTERED-TIMESTAMPxxxxx|9999 |9 |9 |9 |9 |9.0 ij> -- jdbc escape sequences values ({d '1999-01-12'}, {t '11:26:35'}, {ts 'xxxxxxFILTERED-TIMESTAMPxxxxx}); 1 |2 |3 ---------------------------------------------- 1999-01-12|11:26:35|xxxxxxFILTERED-TIMESTAMPxxxxx ij> values year( {d '1999-01-12'}); 1 ----------- 1999 ij> values hour( {t '11:28:10'}); 1 ----------- 11 ij> values day( {ts 'xxxxxxFILTERED-TIMESTAMPxxxxx}); 1 ----------- 12 ij> drop table source; 0 rows inserted/updated/deleted ij> drop table target; 0 rows inserted/updated/deleted ij> -- random tests for date create table sertest(d date, s Date, o Date); 0 rows inserted/updated/deleted ij> insert into sertest values (date('1992-01-03'), null, null); 1 row inserted/updated/deleted ij> select * from sertest; D |S |O -------------------------------- 1992-01-03|NULL |NULL ij> update sertest set s=d; 1 row inserted/updated/deleted ij> update sertest set o=d; 1 row inserted/updated/deleted ij> insert into sertest values (date( '3245-09-09'), date( '1001-06-07'), date( '1999-01-05')); 1 row inserted/updated/deleted ij> select * from sertest; D |S |O -------------------------------- 1992-01-03|1992-01-03|1992-01-03 3245-09-09|1001-06-07|1999-01-05 ij> select * from sertest where d > s; D |S |O -------------------------------- 3245-09-09|1001-06-07|1999-01-05 ij> update sertest set d=s; 2 rows inserted/updated/deleted ij> -- should get type errors: insert into sertest values (date('3245-09-09'), time('09:30:25'), null); ERROR 42821: Columns of type 'DATE' cannot hold values of type 'TIME'. ij> insert into sertest values (null, null, time('09:30:25')); ERROR 42821: Columns of type 'DATE' cannot hold values of type 'TIME'. ij> insert into sertest values (null, null, timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx)); ERROR 42821: Columns of type 'DATE' cannot hold values of type 'TIMESTAMP'. ij> -- should work... update sertest set d=o; 2 rows inserted/updated/deleted ij> select * from sertest where s is null and o is not null; D |S |O -------------------------------- ij> -- should work select month(s) from sertest where s is not null; 1 ----------- 1 6 ij> select day(o) from sertest; 1 ----------- 3 5 ij> drop table sertest; 0 rows inserted/updated/deleted ij> -- conversion tests drop table convtest; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'CONVTEST' because it does not exist. ij> create table convtest(d date, t time, ts timestamp); 0 rows inserted/updated/deleted ij> insert into convtest values(date('1932-03-21'), time('23:49:52'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx')); 1 row inserted/updated/deleted ij> insert into convtest values(date('0001-03-21'), time('5:22:59'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx')); 1 row inserted/updated/deleted ij> insert into convtest values(null, null, null); 1 row inserted/updated/deleted ij> -- these should fail select CAST (d AS time) from convtest; ERROR 42846: Cannot convert types 'DATE' to 'TIME'. ij> select CAST (t AS date) from convtest; ERROR 42846: Cannot convert types 'TIME' to 'DATE'. ij> -- these should work select CAST (t AS time) from convtest; 1 -------- 23:49:52 05:22:59 NULL ij> select CAST (d AS date) from convtest; 1 ---------- 1932-03-21 0001-03-21 NULL ij> select CAST (ts AS time) from convtest; 1 -------- 10:11:43 23:59:59 NULL ij> select CAST (ts AS date) from convtest; 1 ---------- 1832-09-24 9999-12-31 NULL ij> -- show time and date separately as timestamp will be filtered out select CAST(CAST (ts AS timestamp) AS date), CAST(CAST (ts AS timestamp) AS time) from convtest; 1 |2 ------------------- 1832-09-24|10:11:43 9999-12-31|23:59:59 NULL |NULL ij> -- casting from a time to a timestamp sets the date to current date select 'pass', CAST (CAST(t AS timestamp) AS time) from convtest where CAST(CAST(t AS timestamp) AS date)=current_date; ERROR 42846: Cannot convert types 'TIME' to 'TIMESTAMP'. ij> -- time should be 0 select CAST (CAST (d AS timestamp) AS date), CAST(CAST(d AS timestamp) AS time) from convtest; ERROR 42846: Cannot convert types 'DATE' to 'TIMESTAMP'. ij> -- convert from strings create table convstrtest(d varchar(30), t char(30), ts long varchar); 0 rows inserted/updated/deleted ij> insert into convstrtest values('1932-03-21', '23:49:52', 'xxxxxxFILTERED-TIMESTAMPxxxxx'); 1 row inserted/updated/deleted ij> insert into convstrtest values(null, null, null); 1 row inserted/updated/deleted ij> -- these should fail - note when casting from character string the format has to -- be correct select CAST (d AS time) from convstrtest; 1 -------- ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> select CAST (t AS date) from convstrtest; 1 ---------- ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> select CAST (ts AS time) from convstrtest; ERROR 42846: Cannot convert types 'LONG VARCHAR' to 'TIME'. ij> select CAST (ts AS date) from convstrtest; ERROR 42846: Cannot convert types 'LONG VARCHAR' to 'DATE'. ij> -- these should work select CAST (t AS time) from convstrtest; 1 -------- 23:49:52 NULL ij> select CAST (d AS date) from convstrtest; 1 ---------- 1932-03-21 NULL ij> -- show time and date separately as timestamp will be filtered out select CAST(CAST (ts AS timestamp) AS date), CAST(CAST (ts AS timestamp) AS time) from convstrtest; ERROR 42846: Cannot convert types 'LONG VARCHAR' to 'TIMESTAMP'. ij> -- test aggregates -- sum should fail select sum(d) from convtest; ERROR 42Y22: Aggregate SUM cannot operate on type DATE. ij> select sum(t) from convtest; ERROR 42Y22: Aggregate SUM cannot operate on type TIME. ij> select sum(ts) from convtest; ERROR 42Y22: Aggregate SUM cannot operate on type TIMESTAMP. ij> -- these should work select count(d) from convtest; 1 ----------- 2 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select count(t) from convtest; 1 ----------- 2 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select count(ts) from convtest; 1 ----------- 2 WARNING 01003: Null values were eliminated from the argument of a column function. ij> insert into convtest values(date('0001-03-21'), time('5:22:59'), timestamp('xxxxxxFILTERED-TIMESTAMPxxxxx')); 1 row inserted/updated/deleted ij> -- distinct count should be 2 not 3 select count(distinct d) from convtest; 1 ----------- 2 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select count(distinct t) from convtest; 1 ----------- 2 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select count(distinct ts) from convtest; 1 ----------- 2 WARNING 01003: Null values were eliminated from the argument of a column function. ij> -- min should not be null!!!!!!!! select min(d) from convtest; 1 ---------- 0001-03-21 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select min(t) from convtest; 1 -------- 05:22:59 WARNING 01003: Null values were eliminated from the argument of a column function. ij> -- show time and date separately as timestamp will be filtered out select CAST(CAST (min(ts) AS timestamp) AS date), CAST(CAST (min(ts) AS timestamp) AS time) from convtest; 1 |2 ------------------- 1832-09-24|10:11:43 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select max(d) from convtest; 1 ---------- 1932-03-21 WARNING 01003: Null values were eliminated from the argument of a column function. ij> select max(t) from convtest; 1 -------- 23:49:52 WARNING 01003: Null values were eliminated from the argument of a column function. ij> -- show time and date separately as timestamp will be filtered out select CAST(CAST (max(ts) AS timestamp) AS date), CAST(CAST (max(ts) AS timestamp) AS time) from convtest; 1 |2 ------------------- 9999-12-31|23:59:59 WARNING 01003: Null values were eliminated from the argument of a column function. ij> drop table convtest; 0 rows inserted/updated/deleted ij> drop table convstrtest; 0 rows inserted/updated/deleted ij> create table ts (ts1 timestamp, ts2 timestamp); 0 rows inserted/updated/deleted ij> -- ISO format -- leading zeros may be omited from the month, day and part of the timestamp insert into ts values ('2003-03-05-17.05.43.111111', 'xxxxxxFILTERED-TIMESTAMPxxxxx'); 1 row inserted/updated/deleted ij> insert into ts values ('2003-3-03-17.05.43.111111', '2003-3-03 17:05:43.111111'); 1 row inserted/updated/deleted ij> insert into ts values ('2003-3-2-17.05.43.111111', '2003-3-2 17:05:43.111111'); 1 row inserted/updated/deleted ij> insert into ts values ('2003-03-2-17.05.43.111111', '2003-03-2 17:05:43.111111'); 1 row inserted/updated/deleted ij> insert into ts values ('2003-3-1-17.05.43.1', '2003-3-1 17:05:43.1'); 1 row inserted/updated/deleted ij> insert into ts values ('2003-3-1-17.05.43.12', '2003-3-1 17:05:43.12'); 1 row inserted/updated/deleted ij> insert into ts values ('2003-3-1-17.05.43.123', '2003-3-1 17:05:43.123'); 1 row inserted/updated/deleted ij> insert into ts values ('2003-3-1-17.05.43.1234', '2003-3-1 17:05:43.1234'); 1 row inserted/updated/deleted ij> insert into ts values ('2003-3-1-17.05.43.12345', '2003-3-1 17:05:43.12345'); 1 row inserted/updated/deleted ij> insert into ts values ('2003-3-1-17.05.43.123456', '2003-3-1 17:05:43.123456'); 1 row inserted/updated/deleted ij> insert into ts values ('2003-3-1-17.05.43', '2003-3-1 17:05:43'); 1 row inserted/updated/deleted ij> -- trailing blanks are allowed insert into ts values ('2002-03-05-17.05.43.111111 ', 'xxxxxxFILTERED-TIMESTAMPxxxxx'); 1 row inserted/updated/deleted ij> insert into ts values ('2002-03-05-17.05.43.1 ', 'xxxxxxFILTERED-TIMESTAMPxxxxx'); 1 row inserted/updated/deleted ij> insert into ts values ('2002-03-05-17.05.43 ', 'xxxxxxFILTERED-TIMESTAMPxxxxx'); 1 row inserted/updated/deleted ij> -- UDB allows this by "appending a zero"; so, cloudscape follows insert into ts values ('2003-3-1-17.05.43.', '2003-3-1 17:05:43'); 1 row inserted/updated/deleted ij> insert into ts values ('2003-3-1-17.05.43.0', '2003-3-1 17:05:43.0'); 1 row inserted/updated/deleted ij> insert into ts values ('0003-03-05-17.05.43.111111', 'xxxxxxFILTERED-TIMESTAMPxxxxx'); 1 row inserted/updated/deleted ij> select * from ts; TS1 |TS2 ----------------------------------------------------- xxxxxxFILTERED-TIMESTAMPxxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx xxxxxxFILTERED-TIMESTAMPxxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx xxxxxxFILTERED-TIMESTAMPxxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx xxxxxxFILTERED-TIMESTAMPxxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx xxxxxxFILTERED-TIMESTAMPxxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx xxxxxxFILTERED-TIMESTAMPxxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx xxxxxxFILTERED-TIMESTAMPxxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx xxxxxxFILTERED-TIMESTAMPxxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx xxxxxxFILTERED-TIMESTAMPxxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx xxxxxxFILTERED-TIMESTAMPxxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx xxxxxxFILTERED-TIMESTAMPxxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx xxxxxxFILTERED-TIMESTAMPxxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx xxxxxxFILTERED-TIMESTAMPxxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx xxxxxxFILTERED-TIMESTAMPxxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx xxxxxxFILTERED-TIMESTAMPxxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx xxxxxxFILTERED-TIMESTAMPxxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx xxxxxxFILTERED-TIMESTAMPxxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx ij> select * from ts where ts1=ts2; TS1 |TS2 ----------------------------------------------------- xxxxxxFILTERED-TIMESTAMPxxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx xxxxxxFILTERED-TIMESTAMPxxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx xxxxxxFILTERED-TIMESTAMPxxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx xxxxxxFILTERED-TIMESTAMPxxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx xxxxxxFILTERED-TIMESTAMPxxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx xxxxxxFILTERED-TIMESTAMPxxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx xxxxxxFILTERED-TIMESTAMPxxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx xxxxxxFILTERED-TIMESTAMPxxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx xxxxxxFILTERED-TIMESTAMPxxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx xxxxxxFILTERED-TIMESTAMPxxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx xxxxxxFILTERED-TIMESTAMPxxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx xxxxxxFILTERED-TIMESTAMPxxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx xxxxxxFILTERED-TIMESTAMPxxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx xxxxxxFILTERED-TIMESTAMPxxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx xxxxxxFILTERED-TIMESTAMPxxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx xxxxxxFILTERED-TIMESTAMPxxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx xxxxxxFILTERED-TIMESTAMPxxxxx|xxxxxxFILTERED-TIMESTAMPxxxxx ij> delete from ts; 17 rows inserted/updated/deleted ij> -- should be rejected because leading zero in year is missing insert into ts (ts1) values ('03-03-05-17.05.43.111111'); ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> insert into ts (ts1) values ('103-03-05-17.05.43.111111'); ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> insert into ts (ts1) values ('3-03-05-17.05.43.111111'); ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> -- not valid Time format in the timestamp strings: cloudscape rejects insert into ts (ts1) values ('2003-3-24-13.1.02.566999'); ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> insert into ts (ts1) values ('2003-3-24-13.1.1.569'); ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> insert into ts (ts1) values ('2003-3-24-1.1.1.56'); ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> insert into ts (ts1) values ('2003-3-24-1.1.1'); ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> insert into ts (ts1) values ('2003-3-1-17.05.4.'); ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> insert into ts (ts1) values ('2003-03-05-7.05.43.111111'); 1 row inserted/updated/deleted ij> -- invalid ISO format: cloudscape rejects insert into ts (ts1) values ('2003-3-1 17.05.43.123456'); ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> -- Don't allow more than microseconds in ISO format: cloudscape rejects insert into ts (ts1) values ('2003-03-05-17.05.43.999999999'); ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> insert into ts (ts1) values ('2003-03-05-17.05.43.999999000'); ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> select * from ts; TS1 |TS2 ----------------------------------------------------- xxxxxxFILTERED-TIMESTAMPxxxxx|NULL ij> drop table ts; 0 rows inserted/updated/deleted ij> -- Test the timestamp( d, t) function create table t (datecol date, dateStr varchar(16), timecol time, timeStr varchar(16), expected timestamp); 0 rows inserted/updated/deleted ij> insert into t( dateStr, timeStr) values( '2004-03-04', '12:01:02'); 1 row inserted/updated/deleted ij> insert into t( dateStr, timeStr) values( null, '12:01:03'); 1 row inserted/updated/deleted ij> insert into t( dateStr, timeStr) values( '2004-03-05', null); 1 row inserted/updated/deleted ij> update t set datecol = date( dateStr), timecol = time( timeStr); 3 rows inserted/updated/deleted ij> update t set expected = timestamp( dateStr || ' ' || timeStr) where dateStr is not null and timeStr is not null; 1 row inserted/updated/deleted ij> select dateStr, timeStr from t where (expected is not null and (expected <> timestamp( dateCol, timeCol) or timestamp( dateCol, timeCol) is null)) or (expected is null and timestamp( dateCol, timeCol) is not null); DATESTR |TIMESTR --------------------------------- ij> select dateStr, timeStr from t where (expected is not null and (expected <> timestamp( dateStr, timeStr) or timestamp( dateStr, timeStr) is null)) or (expected is null and timestamp( dateStr, timeStr) is not null); DATESTR |TIMESTR --------------------------------- ij> select dateStr, timeStr from t where (expected is not null and timestamp( dateStr, timeStr) <> timestamp( dateCol, timeCol)) or (expected is null and timestamp( dateStr, timeStr) is not null); DATESTR |TIMESTR --------------------------------- ij> select dateStr, timeStr from t where expected is not null and date( timestamp( dateCol, timeCol)) <> dateCol; DATESTR |TIMESTR --------------------------------- ij> select dateStr, timeStr from t where expected is not null and time( timestamp( dateCol, timeCol)) <> timeCol; DATESTR |TIMESTR --------------------------------- ij> -- Error cases select timestamp( dateCol, dateCol) from t where dateCol is not null; ERROR 42Y95: The 'timestamp' operator with a left operand type of 'DATE' and a right operand type of 'DATE' is not supported. ij> select timestamp( timeCol, timeCol) from t where timeCol is not null; ERROR 42Y95: The 'timestamp' operator with a left operand type of 'TIME' and a right operand type of 'TIME' is not supported. ij> values timestamp( 'xyz', '12:01:02'); 1 -------------------------- ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> values timestamp( '2004-03-04', 'xyz'); 1 -------------------------- ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> drop table t; 0 rows inserted/updated/deleted ij> create table t (t time); 0 rows inserted/updated/deleted ij> -- ISO format: UDB is okay. insert into t values ('17.05.44'); 1 row inserted/updated/deleted ij> insert into t values ('17.05.00'); 1 row inserted/updated/deleted ij> insert into t values ('00.05.43'); 1 row inserted/updated/deleted ij> insert into t values ('00.00.00'); 1 row inserted/updated/deleted ij> -- DB2 keeps '24:00:00' but Cloudcape returns '00:00:00' insert into t values ('24.00.00'); 1 row inserted/updated/deleted ij> -- trailing blanks are allowed insert into t values ('17.05.11 '); 1 row inserted/updated/deleted ij> insert into t values ('17:05:11 '); 1 row inserted/updated/deleted ij> -- seconds can be omitted insert into t values ('1:01'); 1 row inserted/updated/deleted ij> insert into t values ('1:02 '); 1 row inserted/updated/deleted ij> insert into t values ('2.01'); 1 row inserted/updated/deleted ij> insert into t values ('2.02 '); 1 row inserted/updated/deleted ij> -- 11 rows select * from t; T -------- 17:05:44 17:05:00 00:05:43 00:00:00 00:00:00 17:05:11 17:05:11 01:01:00 01:02:00 02:01:00 02:02:00 ij> delete from t; 11 rows inserted/updated/deleted ij> -- end value tests... insert into t values ('24.60.60'); ERROR 22007: The string representation of a datetime value is out of range. ij> insert into t values ('04.00.60'); ERROR 22007: The string representation of a datetime value is out of range. ij> insert into t values ('03.60.00'); ERROR 22007: The string representation of a datetime value is out of range. ij> -- not valid Time string ISO format: HH.MM.SS insert into t values ('07.5.44'); ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> insert into t values ('07.05.4'); ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> insert into t values ('7.5.44'); ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> insert into t values ('7.5.4'); ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> insert into t values ('7.5.0'); ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> insert into t values ('-4.00.00'); ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> insert into t values ('A4.00.00'); ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> insert into t values ('7.5.999'); ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> insert into t values ('07.05.111'); ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> insert into t values ('111.05.11'); ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> insert into t values ('11.115.00'); ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> -- no row select * from t; T -------- ij> drop table t; 0 rows inserted/updated/deleted ij> values time('xxxxxxFILTERED-TIMESTAMPxxxxx'); 1 -------- 16:15:32 ij> values time('2004-04-15-16.15.32.387'); 1 -------- 16:15:32 ij> values time('2004-04-15-16.15.32.387 zz'); ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> values time('x-04-15-16.15.32.387'); ERROR 22007: The syntax of the string representation of a datetime value is incorrect. ij> values date('xxxxxxFILTERED-TIMESTAMPxxxxx'); 1 ---------- 2004-04-15 ij> values date('2004-04-15-16.15.32.387'); 1 ---------- 2004-04-15 ij> values date('2004-04-15-16.15.32.387 zz'); ERROR 22008: '2004-04-15-16.15.32.387 zz' is an invalid argument to the date function. ij> values date('2004-04-15-16.15.32.y'); ERROR 22008: '2004-04-15-16.15.32.y' is an invalid argument to the date function. ij> values time('13:59'); 1 -------- 13:59:00 ij> values time('1:00'); 1 -------- 01:00:00 ij> -- Test unary date and datetime functions. Test with both constant and variable arguments. autocommit off; ij> -- test date(integer) create table t( i int, d date); 0 rows inserted/updated/deleted ij> commit; ij> insert into t values( 1, date(1)),(10, date(10.1)),(365,date(365.1e0)),(366,date(366)),(789,date(789)),(790,date(790)),(791,date(791)); 7 rows inserted/updated/deleted ij> -- should fail insert into t values( 0, date(0)); ERROR 22008: '0' is an invalid argument to the date function. ij> insert into t values( -1, date(-1)); ERROR 22008: '-1' is an invalid argument to the date function. ij> insert into t values( 3652060, date( 3652060)); ERROR 22008: '3652060' is an invalid argument to the date function. ij> select i,d,date(i),date(d) from t order by i; I |D |3 |4 -------------------------------------------- 1 |1970-01-01|1970-01-01|1970-01-01 10 |1970-01-10|1970-01-10|1970-01-10 365 |1970-12-31|1970-12-31|1970-12-31 366 |1971-01-01|1971-01-01|1971-01-01 789 |1972-02-28|1972-02-28|1972-02-28 790 |1972-02-29|1972-02-29|1972-02-29 791 |1972-03-01|1972-03-01|1972-03-01 ij> rollback; ij> insert into t(i) values( 0); 1 row inserted/updated/deleted ij> select date(i) from t; 1 ---------- ERROR 22008: '0' is an invalid argument to the date function. ij> rollback; ij> insert into t(i) values( -1); 1 row inserted/updated/deleted ij> select date(i) from t; 1 ---------- ERROR 22008: '-1' is an invalid argument to the date function. ij> rollback; ij> insert into t(i) values( 3652060); 1 row inserted/updated/deleted ij> select date(i) from t; 1 ---------- ERROR 22008: '3652060' is an invalid argument to the date function. ij> rollback; ij> drop table t; 0 rows inserted/updated/deleted ij> create table t( s varchar(32), d date); 0 rows inserted/updated/deleted ij> commit; ij> insert into t values('1900060', date('1900060')), ('1904060', date('1904060')), ('1904366', date('1904366')), ('2000060', date('2000060')), ('2001060', date('2001060')), ('2001365', date('2001365')); 6 rows inserted/updated/deleted ij> select s,d,date(s) from t order by s; S |D |3 ------------------------------------------------------ 1900060 |1900-03-01|1900-03-01 1904060 |1904-02-29|1904-02-29 1904366 |1904-12-31|1904-12-31 2000060 |2000-02-29|2000-02-29 2001060 |2001-03-01|2001-03-01 2001365 |2001-12-31|2001-12-31 ij> rollback; ij> -- failure cases values( date('2001000')); ERROR 22008: '2001000' is an invalid argument to the date function. ij> values( date('2001366')); ERROR 22008: '2001366' is an invalid argument to the date function. ij> values( date('2000367')); ERROR 22008: '2000367' is an invalid argument to the date function. ij> values( date('xxxxxxx')); ERROR 22008: 'xxxxxxx' is an invalid argument to the date function. ij> insert into t(s) values( '2001000'); 1 row inserted/updated/deleted ij> select date(s) from t; 1 ---------- ERROR 22008: '2001000' is an invalid argument to the date function. ij> rollback; ij> insert into t(s) values( '2001366'); 1 row inserted/updated/deleted ij> select date(s) from t; 1 ---------- ERROR 22008: '2001366' is an invalid argument to the date function. ij> rollback; ij> insert into t(s) values( '2000367'); 1 row inserted/updated/deleted ij> select date(s) from t; 1 ---------- ERROR 22008: '2000367' is an invalid argument to the date function. ij> rollback; ij> insert into t(s) values( 'xxxxxxx'); 1 row inserted/updated/deleted ij> select date(s) from t; 1 ---------- ERROR 22008: 'xxxxxxx' is an invalid argument to the date function. ij> rollback; ij> -- test parameter prepare dateTimePS as 'values( date(cast(? as integer)),timestamp(cast(? as varchar(32))))'; ij> execute dateTimePS using 'values(cast(1 as integer), ''2003-03-05-17.05.43.111111'')'; 1 |2 ------------------------------------- 1970-01-01|2003-03-05-17.05.43.111111 ij> execute dateTimePS using 'values(2, ''20030422190200'')'; 1 |2 ------------------------------------- 1970-01-02|xxxxxxFILTERED-TIMESTAMPxxxxx ij> values( date(date(1)), date(timestamp('2003-03-05-17.05.43.111111'))); 1 |2 --------------------- 1970-01-01|2003-03-05 ij> drop table t; 0 rows inserted/updated/deleted ij> create table t( s varchar(32), ts timestamp, expected timestamp); 0 rows inserted/updated/deleted ij> commit; ij> insert into t(ts) values( timestamp('2003-03-05-17.05.43.111111')); 1 row inserted/updated/deleted ij> select date(ts) from t; 1 ---------- 2003-03-05 ij> rollback; ij> -- Test special unary timestamp function rules: yyyyxxddhhmmss insert into t values('20000228235959', timestamp('20000228235959'), '2000-02-28-23.59.59'), ('20000229000000', timestamp('20000229000000'), '2000-02-29-00.00.00'); 2 rows inserted/updated/deleted ij> select s from t where ts <> expected or timestamp(s) <> expected or timestamp(ts) <> expected; S -------------------------------- ij> rollback; ij> -- invalid values( timestamp('2000 1 1 0 0 0')); ERROR 22008: '2000 1 1 0 0 0' is an invalid argument to the timestamp function. ij> values( timestamp('aaaaaaaaaaaaaa')); ERROR 22008: 'aaaaaaaaaaaaaa' is an invalid argument to the timestamp function. ij> insert into t(s) values('2000 1 1 0 0 0'); 1 row inserted/updated/deleted ij> select timestamp(s) from t; 1 -------------------------- ERROR 22008: '2000 1 1 0 0 0' is an invalid argument to the timestamp function. ij> rollback; ij> insert into t(s) values('aaaaaaaaaaaaaa'); 1 row inserted/updated/deleted ij> select timestamp(s) from t; 1 -------------------------- ERROR 22008: 'aaaaaaaaaaaaaa' is an invalid argument to the timestamp function. ij> rollback; ij> --- Null values in datetime scalar functions create table nulls (t time, d date, ts timestamp); 0 rows inserted/updated/deleted ij> insert into nulls values (null,null,null); 1 row inserted/updated/deleted ij> commit; ij> select year(t) from nulls; ERROR 42X25: The 'EXTRACT YEAR' function is not allowed on the 'TIME' type. ij> select month(t) from nulls; ERROR 42X25: The 'EXTRACT MONTH' function is not allowed on the 'TIME' type. ij> select day(t) from nulls; ERROR 42X25: The 'EXTRACT DAY' function is not allowed on the 'TIME' type. ij> select hour(t) from nulls; 1 ----------- NULL ij> select minute(t) from nulls; 1 ----------- NULL ij> select second(t) from nulls; 1 ----------- NULL ij> select year(d) from nulls; 1 ----------- NULL ij> select month(d) from nulls; 1 ----------- NULL ij> select day(d) from nulls; 1 ----------- NULL ij> select hour(d) from nulls; ERROR 42X25: The 'EXTRACT HOUR' function is not allowed on the 'DATE' type. ij> select minute(d) from nulls; ERROR 42X25: The 'EXTRACT MINUTE' function is not allowed on the 'DATE' type. ij> select second(d) from nulls; ERROR 42X25: The 'EXTRACT SECOND' function is not allowed on the 'DATE' type. ij> select year(ts) from nulls; 1 ----------- NULL ij> select month(ts) from nulls; 1 ----------- NULL ij> select day(ts) from nulls; 1 ----------- NULL ij> select hour(ts) from nulls; 1 ----------- NULL ij> select minute(ts) from nulls; 1 ----------- NULL ij> select second(ts) from nulls; 1 ---------------------- NULL ij> drop table nulls; 0 rows inserted/updated/deleted ij> commit; ij>