datetime.sql

derby database source code.good for you.

	o Date);
insert into sertest values (date('1992-01-03'), null, null);
select * from sertest;
update sertest set s=d;
update sertest set o=d;
insert into sertest values (date( '3245-09-09'), date( '1001-06-07'),
	date( '1999-01-05'));
select * from sertest;
select * from sertest where d > s;
update sertest set d=s;

-- should get type errors:
insert into sertest values (date('3245-09-09'), time('09:30:25'), null);
insert into sertest values (null, null, time('09:30:25'));
insert into sertest values (null, null, timestamp('1745-01-01 09:30:25'));

-- should work...
update sertest set d=o;

select * from sertest where s is null and o is not null;

-- should work
select month(s) from sertest where s is not null;
select day(o) from sertest;

drop table sertest;

-- conversion tests
drop table convtest;
create table convtest(d date, t time, ts timestamp);
insert into convtest values(date('1932-03-21'),  time('23:49:52'), timestamp('1832-09-24 10:11:43.32'));
insert into convtest values(date('0001-03-21'),  time('5:22:59'), timestamp('9999-12-31 23:59:59.999999'));
insert into convtest values(null, null, null);
-- these should fail
select CAST (d AS time) from convtest;
select CAST (t AS date) from convtest;
-- these should work
select CAST (t AS time) from convtest;
select CAST (d AS date) from convtest;
select CAST (ts AS time) from convtest;	
select CAST (ts AS date) from convtest;	
-- 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;	
-- 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;
-- time should be 0
select CAST (CAST (d AS timestamp) AS date),
	CAST(CAST(d AS timestamp) AS time) from convtest;	
-- convert from strings
create table convstrtest(d varchar(30), t char(30), ts long varchar);
insert into convstrtest values('1932-03-21',  '23:49:52', '1832-09-24 10:11:43.32');
insert into convstrtest values(null, null, null);
-- these should fail - note when casting from character string the format has to
-- be correct
select CAST (d AS time) from convstrtest;
select CAST (t AS date) from convstrtest;
select CAST (ts AS time) from convstrtest;	
select CAST (ts AS date) from convstrtest;	
-- these should work
select CAST (t AS time) from convstrtest;
select CAST (d AS date) from convstrtest;
-- 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;	
-- test aggregates
-- sum should fail
select sum(d) from convtest;
select sum(t) from convtest;
select sum(ts) from convtest;
-- these should work
select count(d) from convtest;
select count(t) from convtest;
select count(ts) from convtest;
insert into convtest values(date('0001-03-21'),  time('5:22:59'), timestamp('9999-12-31 23:59:59.999999'));
-- distinct count should be 2 not 3
select count(distinct d) from convtest;
select count(distinct t) from convtest;
select count(distinct ts) from convtest;
-- min should not be null!!!!!!!!
select min(d) from convtest;
select min(t) from convtest;
-- 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;	
select max(d) from convtest;
select max(t) from convtest;
-- 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;
drop table convtest;
drop table convstrtest;

create table ts (ts1 timestamp, ts2 timestamp);

-- 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', '2003-03-05 17:05:43.111111');
insert into ts values ('2003-3-03-17.05.43.111111', '2003-3-03 17:05:43.111111');
insert into ts values ('2003-3-2-17.05.43.111111', '2003-3-2 17:05:43.111111');
insert into ts values ('2003-03-2-17.05.43.111111', '2003-03-2 17:05:43.111111');
insert into ts values ('2003-3-1-17.05.43.1', '2003-3-1 17:05:43.1');
insert into ts values ('2003-3-1-17.05.43.12', '2003-3-1 17:05:43.12');
insert into ts values ('2003-3-1-17.05.43.123', '2003-3-1 17:05:43.123');
insert into ts values ('2003-3-1-17.05.43.1234', '2003-3-1 17:05:43.1234');
insert into ts values ('2003-3-1-17.05.43.12345', '2003-3-1 17:05:43.12345');
insert into ts values ('2003-3-1-17.05.43.123456', '2003-3-1 17:05:43.123456');
insert into ts values ('2003-3-1-17.05.43', '2003-3-1 17:05:43');

-- trailing blanks are allowed
insert into ts values ('2002-03-05-17.05.43.111111   ', '2002-03-05 17:05:43.111111   ');
insert into ts values ('2002-03-05-17.05.43.1   ', '2002-03-05 17:05:43.1   ');
insert into ts values ('2002-03-05-17.05.43    ', '2002-03-05 17:05:43    ');

-- 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'); 

insert into ts values ('2003-3-1-17.05.43.0', '2003-3-1 17:05:43.0'); 

insert into ts values ('0003-03-05-17.05.43.111111', '0003-03-05 17:05:43.111111');

select * from ts;

select * from ts where ts1=ts2;

delete from ts;

-- should be rejected because leading zero in year is missing
insert into ts (ts1) values ('03-03-05-17.05.43.111111');
insert into ts (ts1) values ('103-03-05-17.05.43.111111');
insert into ts (ts1) values ('3-03-05-17.05.43.111111');

-- not valid Time format in the timestamp strings: cloudscape rejects
insert into ts (ts1) values ('2003-3-24-13.1.02.566999');
insert into ts (ts1) values ('2003-3-24-13.1.1.569');
insert into ts (ts1) values ('2003-3-24-1.1.1.56');
insert into ts (ts1) values ('2003-3-24-1.1.1');
insert into ts (ts1) values ('2003-3-1-17.05.4.'); 
insert into ts (ts1) values ('2003-03-05-7.05.43.111111');

-- invalid ISO format: cloudscape rejects
insert into ts (ts1) values ('2003-3-1 17.05.43.123456'); 

-- Don't allow more than microseconds in ISO format: cloudscape rejects
insert into ts (ts1) values ('2003-03-05-17.05.43.999999999'); 
insert into ts (ts1) values ('2003-03-05-17.05.43.999999000'); 

select * from ts;

drop table ts;

-- Test the timestamp( d, t) function
create table t (datecol date, dateStr varchar(16), timecol time, timeStr varchar(16), expected timestamp);
insert into t( dateStr, timeStr) values( '2004-03-04', '12:01:02');
insert into t( dateStr, timeStr) values( null, '12:01:03');
insert into t( dateStr, timeStr) values( '2004-03-05', null);
update t set datecol = date( dateStr), timecol = time( timeStr);
update t set expected = timestamp( dateStr || ' ' || timeStr) where dateStr is not null and timeStr is not null;
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);
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);
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);
select dateStr, timeStr from t
  where expected is not null and date( timestamp( dateCol, timeCol)) <> dateCol;
select dateStr, timeStr from t
  where expected is not null and time( timestamp( dateCol, timeCol)) <> timeCol;
-- Error cases
select timestamp( dateCol, dateCol) from t where dateCol is not null;
select timestamp( timeCol, timeCol) from t where timeCol is not null;
values timestamp( 'xyz', '12:01:02');
values timestamp( '2004-03-04', 'xyz');
drop table t;

create table t (t time);

-- ISO format: UDB is okay.
insert into t values ('17.05.44');
insert into t values ('17.05.00');
insert into t values ('00.05.43');
insert into t values ('00.00.00');
-- DB2 keeps '24:00:00' but Cloudcape returns '00:00:00'
insert into t values ('24.00.00');

-- trailing blanks are allowed
insert into t values ('17.05.11  ');
insert into t values ('17:05:11  ');

-- seconds can be omitted
insert into t values ('1:01');
insert into t values ('1:02 ');
insert into t values ('2.01');
insert into t values ('2.02 ');

-- 11 rows
select * from t;

delete from t;

-- end value tests...
insert into t values ('24.60.60');
insert into t values ('04.00.60');
insert into t values ('03.60.00');

-- not valid Time string ISO format: HH.MM.SS
insert into t values ('07.5.44');
insert into t values ('07.05.4');
insert into t values ('7.5.44');
insert into t values ('7.5.4');
insert into t values ('7.5.0');
insert into t values ('-4.00.00');

insert into t values ('A4.00.00');
insert into t values ('7.5.999');
insert into t values ('07.05.111');
insert into t values ('111.05.11');
insert into t values ('11.115.00');

-- no row
select * from t;

drop table t;

values time('2004-04-15 16:15:32.387');
values time('2004-04-15-16.15.32.387');
values time('2004-04-15-16.15.32.387 zz');
values time('x-04-15-16.15.32.387');

values date('2004-04-15 16:15:32.387');
values date('2004-04-15-16.15.32.387');
values date('2004-04-15-16.15.32.387 zz');
values date('2004-04-15-16.15.32.y');

values time('13:59');
values time('1:00');

-- Test unary date and datetime functions. Test with both constant and variable arguments.

autocommit off;

-- test date(integer)
create table t( i int, d date);
commit;

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

-- should fail
insert into t values( 0, date(0));
insert into t values( -1, date(-1));
insert into t values( 3652060, date( 3652060));
select i,d,date(i),date(d) from t order by i;

rollback;
insert into t(i) values( 0);
select date(i) from t;

rollback;
insert into t(i) values( -1);
select date(i) from t;


rollback;
insert into t(i) values( 3652060);
select date(i) from t;

rollback;

drop table t;
create table t( s varchar(32), d date);
commit;

insert into t values('1900060', date('1900060')),
                    ('1904060', date('1904060')),
                    ('1904366', date('1904366')),
                    ('2000060', date('2000060')),
                    ('2001060', date('2001060')),
                    ('2001365', date('2001365'));
select s,d,date(s) from t order by s;
rollback;

-- failure cases
values( date('2001000'));
values( date('2001366'));
values( date('2000367'));
values( date('xxxxxxx'));

insert into t(s) values( '2001000');
select date(s) from t;
rollback;

insert into t(s) values( '2001366');
select date(s) from t;
rollback;

insert into t(s) values( '2000367');
select date(s) from t;
rollback;

insert into t(s) values( 'xxxxxxx');
select date(s) from t;
rollback;

-- test parameter
prepare dateTimePS as 'values( date(cast(? as integer)),timestamp(cast(? as varchar(32))))';
execute dateTimePS using 'values(cast(1 as integer), ''2003-03-05-17.05.43.111111'')';
execute dateTimePS using 'values(2, ''20030422190200'')';

values( date(date(1)), date(timestamp('2003-03-05-17.05.43.111111')));

drop table t;
create table t( s varchar(32), ts timestamp, expected timestamp);
commit;

insert into t(ts) values( timestamp('2003-03-05-17.05.43.111111'));
select date(ts) from t;
rollback;

-- 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');
select s from t where ts <> expected or timestamp(s) <> expected or timestamp(ts) <> expected;
rollback;

-- invalid
values( timestamp('2000 1 1 0 0 0'));
values( timestamp('aaaaaaaaaaaaaa'));

insert into t(s) values('2000 1 1 0 0 0');
select timestamp(s) from t;
rollback;

insert into t(s) values('aaaaaaaaaaaaaa');
select timestamp(s) from t;
rollback;