~drizzle-trunk/drizzle/development

#

# time functions

#

--disable_warnings

drop table if exists t1,t2,t3;

--enable_warnings

select from_days(to_days("960101")),to_days(960201)-to_days("19960101"),to_days(date_add(curdate(), interval 1 day))-to_days(curdate()),weekday("1997-11-29");

select period_add("9602",-12),period_diff(199505,"9404") ;

select now()-now(),weekday(curdate())-weekday(now()),unix_timestamp()-unix_timestamp(now());

select from_unixtime(unix_timestamp("1994-03-02 10:11:12")),from_unixtime(unix_timestamp("1994-03-02 10:11:12"),"%Y-%m-%d %h:%i:%s"),from_unixtime(unix_timestamp("1994-03-02 10:11:12"))+0;

select date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w");

select date_format("1997-01-02", concat("%M %W %D ","%Y %y %m %d %h %i %s %w"));

select dayofmonth("1997-01-02"),dayofmonth(19970323);

select month("1997-01-02"),year("98-02-03"),dayofyear("1997-12-31");

--error 1686 # No more bad dates!

select month("2001-02-00"),year("2001-01-01");

select DAYOFYEAR("1997-03-03"), QUARTER(980303);

select HOUR("1997-03-03 23:03:22"), MINUTE("23:03:22"), SECOND(230322);

select date_format('1998-12-31','%x-%v'),date_format('1999-01-01','%x-%v');

select date_format('1999-12-31','%x-%v'),date_format('2000-01-01','%x-%v');

select dayname("1962-03-03"),dayname("1962-03-03")+0;

select monthname("1972-03-04"),monthname("1972-03-04")+0;

select time_format(19980131000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T');

select time_format(19980131010203,'%H|%I|%k|%l|%i|%p|%r|%S|%T');

select time_format(19980131131415,'%H|%I|%k|%l|%i|%p|%r|%S|%T');

select time_format(19980131010015,'%H|%I|%k|%l|%i|%p|%r|%S|%T');

select date_format(concat('19980131',131415),'%H|%I|%k|%l|%i|%p|%r|%S|%T| %M|%W|%D|%Y|%y|%a|%b|%j|%m|%d|%h|%s|%w');

select date_format(19980021000000,'%H|%I|%k|%l|%i|%p|%r|%S|%T| %M|%W|%D|%Y|%y|%a|%b|%j|%m|%d|%h|%s|%w');

select date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND);

select date_add("1997-12-31 23:59:59",INTERVAL 1 MINUTE);

select date_add("1997-12-31 23:59:59",INTERVAL 1 HOUR);

select date_add("1997-12-31 23:59:59",INTERVAL 1 DAY);

select date_add("1997-12-31 23:59:59",INTERVAL 1 MONTH);

select date_add("1997-12-31 23:59:59",INTERVAL 1 YEAR);

select date_add("1997-12-31 23:59:59",INTERVAL "1:1" MINUTE_SECOND);

select date_add("1997-12-31 23:59:59",INTERVAL "1:1" HOUR_MINUTE);

select date_add("1997-12-31 23:59:59",INTERVAL "1:1" DAY_HOUR);

select date_add("1997-12-31 23:59:59",INTERVAL "1 1" YEAR_MONTH);

select date_add("1997-12-31 23:59:59",INTERVAL "1:1:1" HOUR_SECOND);

select date_add("1997-12-31 23:59:59",INTERVAL "1 1:1" DAY_MINUTE);

select date_add("1997-12-31 23:59:59",INTERVAL "1 1:1:1" DAY_SECOND);

select date_sub("1998-01-01 00:00:00",INTERVAL 1 SECOND);

select date_sub("1998-01-01 00:00:00",INTERVAL 1 MINUTE);

select date_sub("1998-01-01 00:00:00",INTERVAL 1 HOUR);

select date_sub("1998-01-01 00:00:00",INTERVAL 1 DAY);

select date_sub("1998-01-01 00:00:00",INTERVAL 1 MONTH);

select date_sub("1998-01-01 00:00:00",INTERVAL 1 YEAR);

select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" MINUTE_SECOND);

select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" HOUR_MINUTE);

select date_sub("1998-01-01 00:00:00",INTERVAL "1:1" DAY_HOUR);

select date_sub("1998-01-01 00:00:00",INTERVAL "1 1" YEAR_MONTH);

select date_sub("1998-01-01 00:00:00",INTERVAL "1:1:1" HOUR_SECOND);

select date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1" DAY_MINUTE);

select date_sub("1998-01-01 00:00:00",INTERVAL "1 1:1:1" DAY_SECOND);

select date_add("1997-12-31 23:59:59",INTERVAL 100000 SECOND);

select date_add("1997-12-31 23:59:59",INTERVAL -100000 MINUTE);

select date_add("1997-12-31 23:59:59",INTERVAL 100000 HOUR);

select date_add("1997-12-31 23:59:59",INTERVAL -100000 DAY);

select date_add("1997-12-31 23:59:59",INTERVAL 100000 MONTH);

select date_add("1997-12-31 23:59:59",INTERVAL -100000 YEAR);

select date_add("1997-12-31 23:59:59",INTERVAL "10000:1" MINUTE_SECOND);

select date_add("1997-12-31 23:59:59",INTERVAL "-10000:1" HOUR_MINUTE);

select date_add("1997-12-31 23:59:59",INTERVAL "10000:1" DAY_HOUR);

select date_add("1997-12-31 23:59:59",INTERVAL "-100 1" YEAR_MONTH);

select date_add("1997-12-31 23:59:59",INTERVAL "10000:99:99" HOUR_SECOND);

select date_add("1997-12-31 23:59:59",INTERVAL " -10000 99:99" DAY_MINUTE);

select date_add("1997-12-31 23:59:59",INTERVAL "10000 99:99:99" DAY_SECOND);

select "1997-12-31 23:59:59" + INTERVAL 1 SECOND;

select INTERVAL 1 DAY + "1997-12-31";

select "1998-01-01 00:00:00" - INTERVAL 1 SECOND;

select date_sub("1998-01-02",INTERVAL 31 DAY);

select date_add("1997-12-31",INTERVAL 1 SECOND);

select date_add("1997-12-31",INTERVAL 1 DAY);

select date_add(NULL,INTERVAL 100000 SECOND);

select date_add("1997-12-31 23:59:59",INTERVAL NULL SECOND);

select date_add("1997-12-31 23:59:59",INTERVAL NULL MINUTE_SECOND);

select date_add("9999-12-31 23:59:59",INTERVAL 1 SECOND);

select date_sub("0000-00-00 00:00:00",INTERVAL 1 SECOND);

select date_add('1998-01-30',Interval 1 month);

select date_add('1998-01-30',Interval '2:1' year_month);

select date_add('1996-02-29',Interval '1' year);

select extract(YEAR FROM "1999-01-02 10:11:12");

select extract(YEAR_MONTH FROM "1999-01-02");

select extract(DAY FROM "1999-01-02");

select extract(DAY_HOUR FROM "1999-01-02 10:11:12");

# The following is an interval, not a time!

--error 1686 # Bad datetime

select extract(DAY_MINUTE FROM "02 10:11:12");

# The following is an interval, not a time!

--error 1686 # Bad datetime

select extract(DAY_SECOND FROM "225 10:11:12");

select extract(HOUR FROM "1999-01-02 10:11:12");

select extract(HOUR_MINUTE FROM "10:11:12");

select extract(HOUR_SECOND FROM "10:11:12");

select extract(MINUTE FROM "10:11:12");

select extract(MINUTE_SECOND FROM "10:11:12");

select extract(SECOND FROM "1999-01-02 10:11:12");

--error 1686 # Bad datetime

select extract(MONTH FROM "2001-02-00");

#

# test EXTRACT QUARTER (Bug #18100)

#

SELECT EXTRACT(QUARTER FROM '2004-01-15') AS quarter;

SELECT EXTRACT(QUARTER FROM '2004-02-15') AS quarter;

SELECT EXTRACT(QUARTER FROM '2004-03-15') AS quarter;

SELECT EXTRACT(QUARTER FROM '2004-04-15') AS quarter;

SELECT EXTRACT(QUARTER FROM '2004-05-15') AS quarter;

SELECT EXTRACT(QUARTER FROM '2004-06-15') AS quarter;

SELECT EXTRACT(QUARTER FROM '2004-07-15') AS quarter;

SELECT EXTRACT(QUARTER FROM '2004-08-15') AS quarter;

SELECT EXTRACT(QUARTER FROM '2004-09-15') AS quarter;

SELECT EXTRACT(QUARTER FROM '2004-10-15') AS quarter;

SELECT EXTRACT(QUARTER FROM '2004-11-15') AS quarter;

SELECT EXTRACT(QUARTER FROM '2004-12-15') AS quarter;

#

# Test big intervals (Bug #3498)

#

SELECT "1900-01-01 00:00:00" + INTERVAL 2147483648 SECOND;

SELECT "1900-01-01 00:00:00" + INTERVAL "1:2147483647" MINUTE_SECOND;

SELECT "1900-01-01 00:00:00" + INTERVAL "100000000:214748364700" MINUTE_SECOND;

SELECT "1900-01-01 00:00:00" + INTERVAL "1000000000:214748364700" MINUTE_SECOND;

#

# Bug #614 (multiple extracts in where)

#

create table t1 (ctime varchar(20));

insert into t1 values ('2001-01-12 12:23:40');

select ctime, hour(ctime) from t1;

select ctime from t1 where extract(MONTH FROM ctime) = 1 AND extract(YEAR FROM ctime) = 2001;

drop table t1;

#

# Test bug with monthname() and NULL

#

create table t1 (id int);

create table t2 (id int, date date);

insert into t1 values (1);

insert into t2 values (1, NULL);

insert into t1 values (2);

insert into t2 values (2, "2000-01-01");

select monthname(date) from t1 inner join t2 on t1.id = t2.id;

select monthname(date) from t1 inner join t2 on t1.id = t2.id order by t1.id;

drop table t1,t2;

#

# Test bug with month() and year() on text fields with wrong information

CREATE TEMPORARY TABLE t1 (updated text) ENGINE=MyISAM;

INSERT INTO t1 VALUES ('');

--error 1686 # "" is not a date!

SELECT month(updated) from t1;

--error 1686 # "" is not a date for pete's sake.

SELECT year(updated) from t1;

drop table t1;

#

# Check that functions work identically on 0000-00-00 as a constant and on a

# column

#

create table t1 (d date, dt datetime, t timestamp, c char(10));

insert into t1 values (null, null, null, null);

--error 1686 # Bad dates...

insert into t1 values ("0000-00-00", "0000-00-00", "0000-00-00", "0000-00-00");

--error 1686 # 0000-00-00 is a bad date

select dayofyear("0000-00-00"),dayofyear(d),dayofyear(dt),dayofyear(t),dayofyear(c) from t1;

--error 1686 # 0000-00-00 is a bad date

select dayofmonth("0000-00-00"),dayofmonth(d),dayofmonth(dt),dayofmonth(t),dayofmonth(c) from t1;

--error 1686 # 0000-00-00 is not a damn date.

select month("0000-00-00"),month(d),month(dt),month(t),month(c) from t1;

--error 1686 # 0000-00-00 not a date

select quarter("0000-00-00"),quarter(d),quarter(dt),quarter(t),quarter(c) from t1;

--error 1686 # Argh.  0000-00-00 is not a date.

select year("0000-00-00"),year(d),year(dt),year(t),year(c) from t1;

--error 1686 # Bad datetime

select to_days("0000-00-00"),to_days(d),to_days(dt),to_days(c) from t1;

--error 1686 # Ugh. bad datetime

select extract(MONTH FROM "0000-00-00"),extract(MONTH FROM d),extract(MONTH FROM dt),extract(MONTH FROM t),extract(MONTH FROM c) from t1;

drop table t1;

#

# Test problem with TIMESTAMP and BETWEEN

#

CREATE TABLE t1 ( start datetime default NULL);

INSERT INTO t1 VALUES ('2002-10-21 00:00:00'),('2002-10-28 00:00:00'),('2002-11-04 00:00:00');

CREATE TABLE t2 ( ctime1 timestamp NOT NULL, ctime2 timestamp NOT NULL);

INSERT INTO t2 VALUES (20021029165106,20021105164731);

CREATE TABLE t3 (ctime1 char(19) NOT NULL, ctime2 char(19) NOT NULL);

INSERT INTO t3 VALUES ("2002-10-29 16:51:06","2002-11-05 16:47:31");

# The following statement should be fixed to return a row in 4.1

select * from t1, t2 where t1.start between t2.ctime1 and t2.ctime2;

select * from t1, t2 where t1.start >= t2.ctime1 and t1.start <= t2.ctime2;

select * from t1, t3 where t1.start between t3.ctime1 and t3.ctime2;

drop table t1,t2,t3;

#

# Test unix timestamp

#

select @a:=FROM_UNIXTIME(1);

select unix_timestamp(@a);

select unix_timestamp('1970-01-01 00:00:01');

#

# Tests for bug #6439 "unix_timestamp() function returns wrong datetime 

# values for too big argument", bug #7515 "from_unixtime(0) now

# returns NULL instead of the epoch" and bug #9191

# "TIMESTAMP/from_unixtime() no longer accepts 2^31-1."

# unix_timestamp() should return error for too big or negative argument.

# It should return Epoch value for zero argument since it seems that many

# users rely on this fact, from_unixtime() should work with values

# up to INT_MAX32 because of the same reason.

#

select from_unixtime(-1);

# check for from_unixtime(2^31-1) and from_unixtime(2^31)

select from_unixtime(2147483647);

select from_unixtime(2147483648);

select from_unixtime(0);

#

# Some more tests for bug #9191 "TIMESTAMP/from_unixtime() no

# longer accepts 2^31-1". Here we test that from_unixtime and

# unix_timestamp are consistent, when working with boundary dates.

#

select unix_timestamp(from_unixtime(2147483647));

select unix_timestamp(from_unixtime(2147483648));

# check for invalid dates

# bad year

--error 1685 # Bad timestamp

select unix_timestamp('2039-01-20 01:00:00');

--error 1685 # Bad timestamp

select unix_timestamp('1968-01-20 01:00:00');

# bad month

--error 1685 # Bad timestamp

select unix_timestamp('2038-02-10 01:00:00');

--error 1685 # Bad timestamp

select unix_timestamp('1969-11-20 01:00:00');

# bad day

--error 1685 # Bad timestamp

select unix_timestamp('2038-01-20 01:00:00');

--error 1685 # Bad timestamp

select unix_timestamp('1969-12-30 01:00:00');

#

# Check negative shift (we subtract several days for boundary dates during

# conversion).

select unix_timestamp('2038-01-17 12:00:00');

# check bad date, close to the boundary (we cut them off in the very end)

select unix_timestamp('2038-01-19 03:14:07');

#

# Test types from + INTERVAL

#

CREATE TABLE t1 (datetime datetime, timestamp timestamp, date date);

INSERT INTO t1 values ("2001-01-02 03:04:05", "2002-01-02 03:04:05", "2003-01-02");

SELECT * from t1;

select date_add("1997-12-31",INTERVAL 1 SECOND);

select date_add("1997-12-31",INTERVAL "1 1" YEAR_MONTH);

select date_add(datetime, INTERVAL 1 SECOND) from t1;

select date_add(datetime, INTERVAL 1 YEAR) from t1;

select date_add(date,INTERVAL 1 SECOND) from t1;

select date_add(date,INTERVAL 1 MINUTE) from t1;

select date_add(date,INTERVAL 1 HOUR) from t1;

select date_add(date,INTERVAL 1 DAY) from t1;

select date_add(date,INTERVAL 1 MONTH) from t1;

select date_add(date,INTERVAL 1 YEAR) from t1;

select date_add(date,INTERVAL "1:1" MINUTE_SECOND) from t1;

select date_add(date,INTERVAL "1:1" HOUR_MINUTE) from t1;

select date_add(date,INTERVAL "1:1" DAY_HOUR) from t1;

select date_add(date,INTERVAL "1 1" YEAR_MONTH) from t1;

select date_add(date,INTERVAL "1:1:1" HOUR_SECOND) from t1;

select date_add(date,INTERVAL "1 1:1" DAY_MINUTE) from t1;

select date_add(date,INTERVAL "1 1:1:1" DAY_SECOND) from t1;

select date_add(date,INTERVAL "1" WEEK) from t1;

select date_add(date,INTERVAL "1" QUARTER) from t1;

select timestampadd(MINUTE, 1, date) from t1;

select timestampadd(WEEK, 1, date) from t1;

select timestampadd(SQL_TSI_SECOND, 1, date) from t1;

# mysqltest.c discards an expected 'deprecated' warning on prepare stage

--disable_ps_protocol

select timestampadd(SQL_TSI_FRAC_SECOND, 1, date) from t1;

--enable_ps_protocol

select timestampdiff(MONTH, '2001-02-01', '2001-05-01') as a;

select timestampdiff(YEAR, '2002-05-01', '2001-01-01') as a;

select timestampdiff(QUARTER, '2002-05-01', '2001-01-01') as a;

select timestampdiff(MONTH, '2000-03-28', '2000-02-29') as a;

select timestampdiff(MONTH, '1991-03-28', '2000-02-29') as a;

select timestampdiff(SQL_TSI_WEEK, '2001-02-01', '2001-05-01') as a;

select timestampdiff(SQL_TSI_HOUR, '2001-02-01', '2001-05-01') as a;

select timestampdiff(SQL_TSI_DAY, '2001-02-01', '2001-05-01') as a;

select timestampdiff(SQL_TSI_MINUTE, '2001-02-01 12:59:59', '2001-05-01 12:58:59') as a;

select timestampdiff(SQL_TSI_SECOND, '2001-02-01 12:59:59', '2001-05-01 12:58:58') as a;

# mysqltest.c discards an expected 'deprecated' warning on prepare stage

--disable_ps_protocol

select timestampdiff(SQL_TSI_FRAC_SECOND, '2001-02-01 12:59:59.120000', '2001-05-01 12:58:58.119999') as a;

--enable_ps_protocol

select timestampdiff(SQL_TSI_DAY, '1986-02-01', '1986-03-01') as a1,

       timestampdiff(SQL_TSI_DAY, '1900-02-01', '1900-03-01') as a2,

       timestampdiff(SQL_TSI_DAY, '1996-02-01', '1996-03-01') as a3,

       timestampdiff(SQL_TSI_DAY, '2000-02-01', '2000-03-01') as a4;

# bug 16226

SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:27');

SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:28');

SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:29');

SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:27');

SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:28');

SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-12 14:30:29');

SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:27');

SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:28');

SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-17 14:30:29');

SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:27');

SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:28');

SELECT TIMESTAMPDIFF(week,'2006-01-10 14:30:28','2006-01-24 14:30:29');

SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:27');

SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:28');

SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-02-10 14:30:29');

SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:27');

SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:28');

SELECT TIMESTAMPDIFF(month,'2006-01-10 14:30:28','2006-03-10 14:30:29');

SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:27');

SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:28');

SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2007-01-10 14:30:29');

SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:27');

SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:28');

SELECT TIMESTAMPDIFF(year,'2006-01-10 14:30:28','2008-01-10 14:30:29');

# end of bug

drop table t1;

# test for last_day

select last_day('2000-02-05');

select last_day('2002-12-31');

--error 1686 # Bad date

select last_day('2003-03-32');

select last_day('2003-04-01');

select last_day('2001-01-01 01:01:01');

select last_day(NULL);

select last_day('2001-02-12');

create table t1 select last_day('2000-02-05') as a,

                from_days(to_days("960101")) as b;

describe t1;

select * from t1;

drop table t1;

select last_day('2000-02-05') as a,

       from_days(to_days("960101")) as b;

select date_add(last_day("1997-12-1"), INTERVAL 1 DAY);

select length(last_day("1997-12-1"));

select last_day("1997-12-1")+0;

select last_day("1997-12-1")+0.0;

# Test SAPDB UTC_% functions. This part is TZ dependant (It is supposed that

# TZ variable set to GMT-3

select strcmp(date_sub(localtimestamp(), interval 0 hour), utc_timestamp())=0;

select strcmp(date_format(date_sub(localtimestamp(), interval 0 hour),"%Y-%m-%d"), utc_date())=0;

select strcmp(date_format(utc_timestamp(),"%Y-%m-%d"), utc_date())=0;

explain extended select period_add("9602",-12),period_diff(199505,"9404"),from_days(to_days("960101")),dayofmonth("1997-01-02"), month("1997-01-02"), monthname("1972-03-04"),dayofyear("0000-00-00"),HOUR("1997-03-03 23:03:22"),MINUTE("23:03:22"),SECOND(230322),QUARTER(980303),weekday(curdate())-weekday(now()),dayname("1962-03-03"),unix_timestamp(),curdate(),utc_date(),utc_timestamp(),date_format("1997-01-02 03:04:05", "%M %W %D %Y %y %m %d %h %i %s %w"),from_unixtime(unix_timestamp("1994-03-02 10:11:12")),"1997-12-31 23:59:59" + INTERVAL 1 SECOND,"1998-01-01 00:00:00" - INTERVAL 1 SECOND,INTERVAL 1 DAY + "1997-12-31", extract(YEAR FROM "1999-01-02 10:11:12"),date_add("1997-12-31 23:59:59",INTERVAL 1 SECOND);

SET @TMP='2007-08-01 12:22:49';

CREATE TABLE t1 (d DATETIME);

INSERT INTO t1 VALUES ('2007-08-01 12:22:59');

INSERT INTO t1 VALUES ('2007-08-01 12:23:01');

INSERT INTO t1 VALUES ('2007-08-01 12:23:20');

SELECT count(*) FROM t1 WHERE d>FROM_DAYS(TO_DAYS(@TMP)) AND d<=FROM_DAYS(TO_DAYS(@TMP)+1);

DROP TABLE t1;

#

# Bug #10568

#

--error 1686 # Bad date

select last_day('2005-00-00');

--error 1686 # Bad date

select last_day('2005-00-01');

--error 1686 # Bad date

select last_day('2005-01-00');

#

# Bug#16377 result of DATE/TIME functions were compared as strings which

#           can lead to a wrong result.

# Now wrong dates should be compared only with CAST()

create table t1(f1 date, f3 datetime);

insert into t1 values ("2006-01-01", "2006-01-01 12:01:01");

insert into t1 values ("2006-01-02", "2006-01-02 12:01:02");

select f1 from t1 where f1 between CAST("2006-1-1" as date) and CAST(20060101 as date);

select f1 from t1 where f1 between cast("2006-1-1" as date) and cast("2006.1.1" as date);

select f1 from t1 where date(f1) between cast("2006-1-1" as date) and cast("2006.1.1" as date);

select f3 from t1 where f3 between cast("2006-1-1 12:1:1" as datetime) and cast("2006-1-1 12:1:2" as datetime);

select f3 from t1 where timestamp(f3) between cast("2006-1-1 12:1:1" as datetime) and cast("2006-1-1 12:1:2" as datetime);

select f1 from t1 where cast("2006-1-1" as date) between f1 and f3;

select f1 from t1 where cast("2006-1-1" as date) between date(f1) and date(f3);

select f1 from t1 where cast("2006-1-1" as date) between f1 and cast('zzz' as date);

select f1 from t1 where makedate(2006,1) between date(f1) and date(f3);

select f1 from t1 where makedate(2006,2) between date(f1) and date(f3);

drop table t1;

# check if EXTRACT() handles out-of-range values correctly

--error 1686 # Bad datetime

SELECT EXTRACT(HOUR FROM '100000:02:03');

#

# 21913: DATE_FORMAT() Crashes mysql server if I use it through

#        mysql-connector-j driver.

#

SHOW VARIABLES LIKE 'character_set_results';

CREATE TABLE testBug8868 (field1 DATE, field2 VARCHAR(32));

INSERT INTO testBug8868 VALUES ('2006-09-04', 'abcd');

SELECT DATE_FORMAT(field1,'%b-%e %l:%i%p') as fmtddate, field2 FROM testBug8868;

DROP TABLE testBug8868;

#

# Bug #23653: crash if last_day('0000-00-00')

#

--error 1686 # Bad date

select last_day('0000-00-00');

--echo End of 4.1 tests

explain extended select timestampdiff(SQL_TSI_WEEK, '2001-02-01', '2001-05-01') as a1,

			timestampdiff(SQL_TSI_FRAC_SECOND, '2001-02-01 12:59:59.120000', '2001-05-01 12:58:58.119999') as a2;

#

# Bug #10590: %h, %I, and %l format specifies should all return results in

# the 0-11 range

#

select time_format('100:00:00', '%H %k %h %I %l');

#

# Bug #13534: timestampdiff() returned incorrect results across leap years

#

select timestampdiff(month,'2004-09-11','2004-09-11');

select timestampdiff(month,'2004-09-11','2005-09-11');

select timestampdiff(month,'2004-09-11','2006-09-11');

select timestampdiff(month,'2004-09-11','2007-09-11');

select timestampdiff(month,'2005-09-11','2004-09-11');

select timestampdiff(month,'2005-09-11','2003-09-11');

select timestampdiff(month,'2004-02-28','2005-02-28');

select timestampdiff(month,'2004-02-29','2005-02-28');

select timestampdiff(month,'2004-02-28','2005-02-28');

select timestampdiff(month,'2004-03-29','2005-03-28');

select timestampdiff(month,'2003-02-28','2004-02-29');

select timestampdiff(month,'2003-02-28','2005-02-28');

select timestampdiff(month,'1999-09-11','2001-10-10');

select timestampdiff(month,'1999-09-11','2001-9-11');

select timestampdiff(year,'1999-09-11','2001-9-11');

select timestampdiff(year,'2004-02-28','2005-02-28');

select timestampdiff(year,'2004-02-29','2005-02-28');

#

# Bug #18618: BETWEEN for dates with the second argument being a constant

#             expression and the first and the third arguments being fields 

#

CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, day date);

CREATE TABLE t2 (id int NOT NULL PRIMARY KEY, day date);

INSERT INTO t1 VALUES

  (1, '2005-06-01'), (2, '2005-02-01'), (3, '2005-07-01');

INSERT INTO t2 VALUES

  (1, '2005-08-01'), (2, '2005-06-15'), (3, '2005-07-15');

SELECT * FROM t1, t2 

  WHERE t1.day BETWEEN 

               '2005.09.01' - INTERVAL 6 MONTH AND t2.day;

SELECT * FROM t1, t2 

  WHERE CAST(t1.day AS DATE) BETWEEN 

                             '2005.09.01' - INTERVAL 6 MONTH AND t2.day;

DROP TABLE t1,t2;

#

# Bug #21103: DATE column not compared as DATE

#

create table t1 (field DATE);

insert into t1 values ('2006-11-06');

select * from t1 where field < '2006-11-06 04:08:36.0'; 

select * from t1 where field = '2006-11-06 04:08:36.0'; 

select * from t1 where field = '2006-11-06'; 

select * from t1 where CAST(field as DATETIME) < '2006-11-06 04:08:36.0';

select * from t1 where CAST(field as DATE) < '2006-11-06 04:08:36.0';

drop table t1;

#

# Bug#32180: DATE_ADD treats datetime numeric argument as DATE

#            instead of DATETIME

#

select DATE_ADD('20071108181000', INTERVAL 1 DAY);

select DATE_ADD(20071108181000,   INTERVAL 1 DAY);

select DATE_ADD('20071108',       INTERVAL 1 DAY);

select DATE_ADD(20071108,         INTERVAL 1 DAY);

#

# Bug#32770: LAST_DAY() returns a DATE, but somehow internally keeps

#            track of the TIME.

#

select LAST_DAY('2007-12-06 08:59:19.05') - INTERVAL 1 SECOND;

#

# Bug#33834: FRAC_SECOND: Applicability not clear in documentation

#

# Show that he use of FRAC_SECOND, for anything other than

# TIMESTAMPADD / TIMESTAMPDIFF, is a server error.

# mysqltest.c discards an expected 'deprecated' warning on prepare stage

SELECT TIMESTAMPADD(FRAC_SECOND, 1, '2008-02-18');

SELECT TIMESTAMPDIFF(FRAC_SECOND, '2008-02-17', '2008-02-18');

--error ER_PARSE_ERROR

SELECT DATE_ADD('2008-02-18', INTERVAL 1 FRAC_SECOND);

--error ER_PARSE_ERROR

SELECT DATE_SUB('2008-02-18', INTERVAL 1 FRAC_SECOND);

--error ER_PARSE_ERROR

SELECT '2008-02-18' + INTERVAL 1 FRAC_SECOND;

--error ER_PARSE_ERROR

SELECT '2008-02-18' - INTERVAL 1 FRAC_SECOND;

--echo End of 5.0 tests

#

# Bug #18997

#

select date_sub("0050-01-01 00:00:01",INTERVAL 2 SECOND);

select date_sub("0199-01-01 00:00:01",INTERVAL 2 SECOND);

select date_add("0199-12-31 23:59:59",INTERVAL 2 SECOND);

select date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND);

select date_sub("0200-01-01 00:00:01",INTERVAL 1 SECOND);

select date_sub("0200-01-01 00:00:01",INTERVAL 2 SECOND);

select date_add("2001-01-01 23:59:59",INTERVAL -2000 YEAR);

select date_sub("50-01-01 00:00:01",INTERVAL 2 SECOND);

select date_sub("90-01-01 00:00:01",INTERVAL 2 SECOND);

select date_sub("0069-01-01 00:00:01",INTERVAL 2 SECOND);

select date_sub("0169-01-01 00:00:01",INTERVAL 2 SECOND);

--echo End of 5.1 tests