datetime.out

derby database source code.good for you.

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         
---------------------------------