SET timestamp=UNIX_TIMESTAMP('2001-02-03 10:20:30'); drop table if exists t1, t2, t3; create table t1 (a datetime(7)); ERROR 42000: Too big precision 7 specified for 'a'. Maximum is 6 create table t1 (a datetime(3), key(a)); insert t1 values ('2010-12-11 00:20:03.1234'); insert t1 values ('2010-12-11 15:47:11.1234'); insert t1 values (20101211010203.45678); insert t1 values (20101211030405.789e0); insert ignore t1 values (99991231235959e1); Warnings: Warning 1265 Data truncated for column 'a' at row 1 select * from t1; a 0000-00-00 00:00:00.000 2010-12-11 00:20:03.123 2010-12-11 01:02:03.456 2010-12-11 03:04:05.789 2010-12-11 15:47:11.123 select cast(a AS double(30,6)) from t1; cast(a AS double(30,6)) 0.000000 20101211002003.120000 20101211010203.457031 20101211030405.790000 20101211154711.120000 select a DIV 1 from t1; a DIV 1 0 20101211002003 20101211010203 20101211030405 20101211154711 select group_concat(distinct a) from t1; group_concat(distinct a) 0000-00-00 00:00:00.000,2010-12-11 00:20:03.123,2010-12-11 01:02:03.456,2010-12-11 03:04:05.789,2010-12-11 15:47:11.123 alter table t1 engine=innodb; select * from t1 order by a; a 0000-00-00 00:00:00.000 2010-12-11 00:20:03.123 2010-12-11 01:02:03.456 2010-12-11 03:04:05.789 2010-12-11 15:47:11.123 select * from t1 order by a+0; a 0000-00-00 00:00:00.000 2010-12-11 00:20:03.123 2010-12-11 01:02:03.456 2010-12-11 03:04:05.789 2010-12-11 15:47:11.123 drop table t1; create table t1 (a datetime(4)) engine=innodb; insert t1 values ('2010-12-11 01:02:03.456789'); select * from t1; a 2010-12-11 01:02:03.4567 select extract(microsecond from a + interval 100 microsecond) from t1 where a>'2010-11-12 01:02:03.456'; extract(microsecond from a + interval 100 microsecond) 456800 select a from t1 where a>'2010-11-12 01:02:03.456' group by a; a 2010-12-11 01:02:03.4567 show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` datetime(4) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 show columns from t1; Field Type Null Key Default Extra a datetime(4) YES NULL select table_name, column_name, column_default, is_nullable, data_type, character_maximum_length, character_octet_length, numeric_precision, numeric_scale, datetime_precision, character_set_name, collation_name, column_type, column_key, extra from information_schema.columns where table_name='t1'; table_name t1 column_name a column_default NULL is_nullable YES data_type datetime character_maximum_length NULL character_octet_length NULL numeric_precision NULL numeric_scale NULL datetime_precision 4 character_set_name NULL collation_name NULL column_type datetime(4) column_key extra select a, a+interval 9876543 microsecond from t1; a a+interval 9876543 microsecond 2010-12-11 01:02:03.4567 2010-12-11 01:02:13.333243 update t1 set a=a+interval 9876543 microsecond; select * from t1; a 2010-12-11 01:02:13.3332 select a, a + interval 2 year from t1; a a + interval 2 year 2010-12-11 01:02:13.3332 2012-12-11 01:02:13.3332 insert ignore t1 select a + interval 2 year from t1; select * from t1; a 2010-12-11 01:02:13.3332 2012-12-11 01:02:13.3332 delete from t1 where a < 20110101; select * from t1; a 2012-12-11 01:02:13.3332 create table t2 select * from t1; create table t3 like t1; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a` datetime(4) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show create table t3; Table Create Table t3 CREATE TABLE `t3` ( `a` datetime(4) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 drop table t2, t3; insert t1 values ('2010-12-13 14:15:16.222222'); select a, a+0, a-1, a*1, a/2 from t1; a a+0 a-1 a*1 a/2 2012-12-11 01:02:13.3332 20121211010213.3332 20121211010212.3332 20121211010213.3332 10060605505106.66660000 2010-12-13 14:15:16.2222 20101213141516.2222 20101213141515.2222 20101213141516.2222 10050606570758.11110000 select max(a), min(a), sum(a), avg(a) from t1; max(a) min(a) sum(a) avg(a) 2012-12-11 01:02:13.3332 2010-12-13 14:15:16.2222 40222424151729.5554 20111212075864.77770000 create table t2 select a, a+0, a-1, a*1, a/2 from t1; create table t3 select max(a), min(a), sum(a), avg(a) from t1; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a` datetime(4) DEFAULT NULL, `a+0` decimal(19,4) DEFAULT NULL, `a-1` decimal(19,4) DEFAULT NULL, `a*1` decimal(19,4) DEFAULT NULL, `a/2` decimal(22,8) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show create table t3; Table Create Table t3 CREATE TABLE `t3` ( `max(a)` datetime(4) DEFAULT NULL, `min(a)` datetime(4) DEFAULT NULL, `sum(a)` decimal(40,4) DEFAULT NULL, `avg(a)` decimal(22,8) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1, t2, t3; create table t1 (f0_datetime datetime(0), f1_datetime datetime(1), f2_datetime datetime(2), f3_datetime datetime(3), f4_datetime datetime(4), f5_datetime datetime(5), f6_datetime datetime(6)); insert t1 values ( '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432', '2010-11-12 11:14:17.765432'); select * from t1; f0_datetime 2010-11-12 11:14:17 f1_datetime 2010-11-12 11:14:17.7 f2_datetime 2010-11-12 11:14:17.76 f3_datetime 2010-11-12 11:14:17.765 f4_datetime 2010-11-12 11:14:17.7654 f5_datetime 2010-11-12 11:14:17.76543 f6_datetime 2010-11-12 11:14:17.765432 select cast(f0_datetime as time(4)) time4_f0_datetime, cast(f1_datetime as datetime(3)) datetime3_f1_datetime, cast(f2_datetime as date) date_f2_datetime, cast(f4_datetime as double) double_f3_datetime, cast(f4_datetime as decimal(40,5)) decimal5_f4_datetime, cast(f5_datetime as signed) bigint_f5_datetime, cast(f6_datetime as char(255)) varchar_f6_datetime from t1; time4_f0_datetime 11:14:17.0000 datetime3_f1_datetime 2010-11-12 11:14:17.700 date_f2_datetime 2010-11-12 double_f3_datetime 20101112111417.766 decimal5_f4_datetime 20101112111417.76540 bigint_f5_datetime 20101112111417 varchar_f6_datetime 2010-11-12 11:14:17.765432 create table t2 (time4_f0_datetime time(4), datetime3_f1_datetime datetime(3), date_f2_datetime date, double_f3_datetime double, decimal5_f4_datetime decimal(40,5), bigint_f5_datetime bigint, varchar_f6_datetime varchar(255)); insert t2 select * from t1; Warnings: Level Note Code 1265 Message Data truncated for column 'time4_f0_datetime' at row 1 Level Note Code 1265 Message Data truncated for column 'date_f2_datetime' at row 1 select * from t2; time4_f0_datetime 11:14:17.0000 datetime3_f1_datetime 2010-11-12 11:14:17.700 date_f2_datetime 2010-11-12 double_f3_datetime 20101112111417.766 decimal5_f4_datetime 20101112111417.76540 bigint_f5_datetime 20101112111417 varchar_f6_datetime 2010-11-12 11:14:17.765432 alter table t1 change f0_datetime time4_f0_datetime time(4), change f1_datetime datetime3_f1_datetime datetime(3), change f2_datetime date_f2_datetime date, change f3_datetime double_f3_datetime double, change f4_datetime decimal5_f4_datetime decimal(40,5), change f5_datetime bigint_f5_datetime bigint, change f6_datetime varchar_f6_datetime varchar(255); Warnings: Level Note Code 1265 Message Data truncated for column 'time4_f0_datetime' at row 1 Level Note Code 1265 Message Data truncated for column 'date_f2_datetime' at row 1 select * from t1; time4_f0_datetime 11:14:17.0000 datetime3_f1_datetime 2010-11-12 11:14:17.700 date_f2_datetime 2010-11-12 double_f3_datetime 20101112111417.766 decimal5_f4_datetime 20101112111417.76540 bigint_f5_datetime 20101112111417 varchar_f6_datetime 2010-11-12 11:14:17.765432 alter table t1 modify time4_f0_datetime datetime(0), modify datetime3_f1_datetime datetime(1), modify date_f2_datetime datetime(2), modify double_f3_datetime datetime(3), modify decimal5_f4_datetime datetime(4), modify bigint_f5_datetime datetime(5), modify varchar_f6_datetime datetime(6); select * from t1; time4_f0_datetime 2001-02-03 11:14:17 datetime3_f1_datetime 2010-11-12 11:14:17.7 date_f2_datetime 2010-11-12 00:00:00.00 double_f3_datetime 2010-11-12 11:14:17.766 decimal5_f4_datetime 2010-11-12 11:14:17.7654 bigint_f5_datetime 2010-11-12 11:14:17.00000 varchar_f6_datetime 2010-11-12 11:14:17.765432 delete from t1; insert t1 select * from t2; select * from t1; time4_f0_datetime 2001-02-03 11:14:17 datetime3_f1_datetime 2010-11-12 11:14:17.7 date_f2_datetime 2010-11-12 00:00:00.00 double_f3_datetime 2010-11-12 11:14:17.765 decimal5_f4_datetime 2010-11-12 11:14:17.7654 bigint_f5_datetime 2010-11-12 11:14:17.00000 varchar_f6_datetime 2010-11-12 11:14:17.765432 drop table t1, t2; create table t1 (a datetime(6), b datetime(6)); create procedure foo(x datetime, y datetime(4)) insert into t1 values (x, y); call foo('2010-02-03 4:5:6.789123', '2010-02-03 4:5:6.789123'); select * from t1; a b 2010-02-03 04:05:06.000000 2010-02-03 04:05:06.789100 create procedure bar(a int, c datetime(5)) begin declare b datetime(4); set b = c + interval a microsecond; insert t1 values (b, c + interval a microsecond); end| call bar(1111111, '2011-01-02 3:4:5.123456'); select * from t1; a b 2010-02-03 04:05:06.000000 2010-02-03 04:05:06.789100 2011-01-02 03:04:06.234500 2011-01-02 03:04:06.234561 drop procedure foo; drop procedure bar; create function xyz(s char(20)) returns datetime(4) return addtime('2010-10-10 10:10:10.101010', s); select xyz('1:1:1.010101'); xyz('1:1:1.010101') 2010-10-10 11:11:11.1111 drop function xyz; create view v1 as select * from t1 group by a,b; select * from v1; a b 2010-02-03 04:05:06.000000 2010-02-03 04:05:06.789100 2011-01-02 03:04:06.234500 2011-01-02 03:04:06.234561 show columns from v1; Field Type Null Key Default Extra a datetime(6) YES NULL b datetime(6) YES NULL create table t2 select * from v1; show create table t2; Table Create Table t2 CREATE TABLE `t2` ( `a` datetime(6) DEFAULT NULL, `b` datetime(6) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 select * from t2; a b 2010-02-03 04:05:06.000000 2010-02-03 04:05:06.789100 2011-01-02 03:04:06.234500 2011-01-02 03:04:06.234561 drop view v1; drop table t1, t2; SET timestamp=DEFAULT; CREATE TABLE t1 ( taken datetime(5) NOT NULL DEFAULT '0000-00-00 00:00:00', id int(11) NOT NULL DEFAULT '0', PRIMARY KEY (id,taken), KEY taken (taken) ) PARTITION BY RANGE (to_days(taken)) ( PARTITION p01 VALUES LESS THAN (732920), PARTITION p02 VALUES LESS THAN (732950), PARTITION p03 VALUES LESS THAN MAXVALUE); INSERT INTO t1 VALUES ('2006-09-27 21:50:01.123456',0), ('2006-09-27 21:50:01.123456',1), ('2006-09-27 21:50:01.123456',2), ('2006-09-28 21:50:01.123456',3), ('2006-09-29 21:50:01.123456',4), ('2006-09-29 21:50:01.123456',5), ('2006-09-30 21:50:01.123456',6), ('2006-10-01 21:50:01.123456',7), ('2006-10-02 21:50:01.123456',8), ('2006-10-02 21:50:01.123456',9); SELECT id,to_days(taken) FROM t1 order by 2; id to_days(taken) 0 732946 1 732946 2 732946 3 732947 5 732948 4 732948 6 732949 7 732950 8 732951 9 732951 CREATE TABLE t2 ( taken datetime(5) NOT NULL DEFAULT '0000-00-00 00:00:00', id int(11) NOT NULL DEFAULT '0', PRIMARY KEY (id,taken), KEY taken (taken) ) PARTITION BY RANGE (extract(microsecond from taken)) ( PARTITION p01 VALUES LESS THAN (123000), PARTITION p02 VALUES LESS THAN (500000), PARTITION p03 VALUES LESS THAN MAXVALUE); INSERT INTO t2 VALUES ('2006-09-27 21:50:01',0), ('2006-09-27 21:50:01.1',1), ('2006-09-27 21:50:01.12',2), ('2006-09-28 21:50:01.123',3), ('2006-09-29 21:50:01.1234',4), ('2006-09-29 21:50:01.12345',5), ('2006-09-30 21:50:01.123456',6), ('2006-10-01 21:50:01.56',7), ('2006-10-02 21:50:01.567',8), ('2006-10-02 21:50:01.5678',9); select table_name,partition_name,partition_method,partition_expression,partition_description,table_rows from information_schema.partitions where table_name in ('t1', 't2'); table_name partition_name partition_method partition_expression partition_description table_rows t1 p01 RANGE to_days(`taken`) 732920 0 t1 p02 RANGE to_days(`taken`) 732950 7 t1 p03 RANGE to_days(`taken`) MAXVALUE 3 t2 p01 RANGE extract(microsecond from `taken`) 123000 3 t2 p02 RANGE extract(microsecond from `taken`) 500000 4 t2 p03 RANGE extract(microsecond from `taken`) MAXVALUE 3 drop table t1, t2; create table t1 (a datetime, b datetime(6)); insert t1 values ('2010-01-02 03:04:05.678912', '2010-01-02 03:04:05.678912'); update t1 set b=a; select * from t1; a b 2010-01-02 03:04:05 2010-01-02 03:04:05.000000 alter table t1 modify b datetime, modify a datetime(6); select * from t1; a b 2010-01-02 03:04:05.000000 2010-01-02 03:04:05 drop table t1; # # MDEV-4651 Crash in my_decimal2decimal in a ORDER BY query # SET @@time_zone='+00:00'; CREATE TABLE t1 (a DATETIME(4) NOT NULL); INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2000-00-00 00:00:00'); SELECT UNIX_TIMESTAMP(a) FROM t1 ORDER BY 1; UNIX_TIMESTAMP(a) NULL 978307200.0000 DROP TABLE t1; SET @@time_zone=DEFAULT; # # Start of 10.4 tests # # # MDEV-20397 Support TIMESTAMP, DATETIME, TIME in ROUND() and TRUNCATE() # CREATE TABLE t1 (a1 DATETIME(6), a2 DATETIME(6) NOT NULL); CREATE TABLE t2 AS SELECT ROUND(a1) AS r1, ROUND(a2) AS r2, TRUNCATE(a1,0) AS t1, TRUNCATE(a2,0) AS t2 FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `r1` datetime DEFAULT NULL, `r2` datetime DEFAULT NULL, `t1` datetime DEFAULT NULL, `t2` datetime NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 (a DATETIME(6)); INSERT INTO t1 VALUES ('0000-00-00 00:00:00.999999'), ('0000-00-00 23:59:59.999999'), ('0000-00-01 00:00:00.999999'), ('0000-00-01 23:59:59.999999'), ('0000-00-31 23:59:59.999999'), ('0000-01-01 00:00:00.999999'), ('0000-01-01 23:59:59.999999'), ('0000-01-31 23:59:59.999999'), ('0000-02-28 23:59:59.999999'), ('0000-12-31 23:59:59.999999'), ('0001-01-01 00:00:00.999999'), ('0001-02-28 23:59:59.999999'), ('0001-12-31 23:59:59.999999'), ('0004-02-28 23:59:59.999999'), ('0004-02-29 23:59:59.999999'), ('2000-02-29 23:59:59.999999'), ('2000-12-31 23:59:59.999999'), ('9999-12-31 23:59:59.999999'); SELECT a, TRUNCATE(a,0) FROM t1; a TRUNCATE(a,0) 0000-00-00 00:00:00.999999 0000-00-00 00:00:00 0000-00-00 23:59:59.999999 0000-00-00 23:59:59 0000-00-01 00:00:00.999999 0000-00-01 00:00:00 0000-00-01 23:59:59.999999 0000-00-01 23:59:59 0000-00-31 23:59:59.999999 0000-00-31 23:59:59 0000-01-01 00:00:00.999999 0000-01-01 00:00:00 0000-01-01 23:59:59.999999 0000-01-01 23:59:59 0000-01-31 23:59:59.999999 0000-01-31 23:59:59 0000-02-28 23:59:59.999999 0000-02-28 23:59:59 0000-12-31 23:59:59.999999 0000-12-31 23:59:59 0001-01-01 00:00:00.999999 0001-01-01 00:00:00 0001-02-28 23:59:59.999999 0001-02-28 23:59:59 0001-12-31 23:59:59.999999 0001-12-31 23:59:59 0004-02-28 23:59:59.999999 0004-02-28 23:59:59 0004-02-29 23:59:59.999999 0004-02-29 23:59:59 2000-02-29 23:59:59.999999 2000-02-29 23:59:59 2000-12-31 23:59:59.999999 2000-12-31 23:59:59 9999-12-31 23:59:59.999999 9999-12-31 23:59:59 SELECT a, TRUNCATE(a,1) FROM t1; a TRUNCATE(a,1) 0000-00-00 00:00:00.999999 0000-00-00 00:00:00.9 0000-00-00 23:59:59.999999 0000-00-00 23:59:59.9 0000-00-01 00:00:00.999999 0000-00-01 00:00:00.9 0000-00-01 23:59:59.999999 0000-00-01 23:59:59.9 0000-00-31 23:59:59.999999 0000-00-31 23:59:59.9 0000-01-01 00:00:00.999999 0000-01-01 00:00:00.9 0000-01-01 23:59:59.999999 0000-01-01 23:59:59.9 0000-01-31 23:59:59.999999 0000-01-31 23:59:59.9 0000-02-28 23:59:59.999999 0000-02-28 23:59:59.9 0000-12-31 23:59:59.999999 0000-12-31 23:59:59.9 0001-01-01 00:00:00.999999 0001-01-01 00:00:00.9 0001-02-28 23:59:59.999999 0001-02-28 23:59:59.9 0001-12-31 23:59:59.999999 0001-12-31 23:59:59.9 0004-02-28 23:59:59.999999 0004-02-28 23:59:59.9 0004-02-29 23:59:59.999999 0004-02-29 23:59:59.9 2000-02-29 23:59:59.999999 2000-02-29 23:59:59.9 2000-12-31 23:59:59.999999 2000-12-31 23:59:59.9 9999-12-31 23:59:59.999999 9999-12-31 23:59:59.9 SELECT a, TRUNCATE(a,2) FROM t1; a TRUNCATE(a,2) 0000-00-00 00:00:00.999999 0000-00-00 00:00:00.99 0000-00-00 23:59:59.999999 0000-00-00 23:59:59.99 0000-00-01 00:00:00.999999 0000-00-01 00:00:00.99 0000-00-01 23:59:59.999999 0000-00-01 23:59:59.99 0000-00-31 23:59:59.999999 0000-00-31 23:59:59.99 0000-01-01 00:00:00.999999 0000-01-01 00:00:00.99 0000-01-01 23:59:59.999999 0000-01-01 23:59:59.99 0000-01-31 23:59:59.999999 0000-01-31 23:59:59.99 0000-02-28 23:59:59.999999 0000-02-28 23:59:59.99 0000-12-31 23:59:59.999999 0000-12-31 23:59:59.99 0001-01-01 00:00:00.999999 0001-01-01 00:00:00.99 0001-02-28 23:59:59.999999 0001-02-28 23:59:59.99 0001-12-31 23:59:59.999999 0001-12-31 23:59:59.99 0004-02-28 23:59:59.999999 0004-02-28 23:59:59.99 0004-02-29 23:59:59.999999 0004-02-29 23:59:59.99 2000-02-29 23:59:59.999999 2000-02-29 23:59:59.99 2000-12-31 23:59:59.999999 2000-12-31 23:59:59.99 9999-12-31 23:59:59.999999 9999-12-31 23:59:59.99 SELECT a, TRUNCATE(a,3) FROM t1; a TRUNCATE(a,3) 0000-00-00 00:00:00.999999 0000-00-00 00:00:00.999 0000-00-00 23:59:59.999999 0000-00-00 23:59:59.999 0000-00-01 00:00:00.999999 0000-00-01 00:00:00.999 0000-00-01 23:59:59.999999 0000-00-01 23:59:59.999 0000-00-31 23:59:59.999999 0000-00-31 23:59:59.999 0000-01-01 00:00:00.999999 0000-01-01 00:00:00.999 0000-01-01 23:59:59.999999 0000-01-01 23:59:59.999 0000-01-31 23:59:59.999999 0000-01-31 23:59:59.999 0000-02-28 23:59:59.999999 0000-02-28 23:59:59.999 0000-12-31 23:59:59.999999 0000-12-31 23:59:59.999 0001-01-01 00:00:00.999999 0001-01-01 00:00:00.999 0001-02-28 23:59:59.999999 0001-02-28 23:59:59.999 0001-12-31 23:59:59.999999 0001-12-31 23:59:59.999 0004-02-28 23:59:59.999999 0004-02-28 23:59:59.999 0004-02-29 23:59:59.999999 0004-02-29 23:59:59.999 2000-02-29 23:59:59.999999 2000-02-29 23:59:59.999 2000-12-31 23:59:59.999999 2000-12-31 23:59:59.999 9999-12-31 23:59:59.999999 9999-12-31 23:59:59.999 SELECT a, TRUNCATE(a,4) FROM t1; a TRUNCATE(a,4) 0000-00-00 00:00:00.999999 0000-00-00 00:00:00.9999 0000-00-00 23:59:59.999999 0000-00-00 23:59:59.9999 0000-00-01 00:00:00.999999 0000-00-01 00:00:00.9999 0000-00-01 23:59:59.999999 0000-00-01 23:59:59.9999 0000-00-31 23:59:59.999999 0000-00-31 23:59:59.9999 0000-01-01 00:00:00.999999 0000-01-01 00:00:00.9999 0000-01-01 23:59:59.999999 0000-01-01 23:59:59.9999 0000-01-31 23:59:59.999999 0000-01-31 23:59:59.9999 0000-02-28 23:59:59.999999 0000-02-28 23:59:59.9999 0000-12-31 23:59:59.999999 0000-12-31 23:59:59.9999 0001-01-01 00:00:00.999999 0001-01-01 00:00:00.9999 0001-02-28 23:59:59.999999 0001-02-28 23:59:59.9999 0001-12-31 23:59:59.999999 0001-12-31 23:59:59.9999 0004-02-28 23:59:59.999999 0004-02-28 23:59:59.9999 0004-02-29 23:59:59.999999 0004-02-29 23:59:59.9999 2000-02-29 23:59:59.999999 2000-02-29 23:59:59.9999 2000-12-31 23:59:59.999999 2000-12-31 23:59:59.9999 9999-12-31 23:59:59.999999 9999-12-31 23:59:59.9999 SELECT a, TRUNCATE(a,5) FROM t1; a TRUNCATE(a,5) 0000-00-00 00:00:00.999999 0000-00-00 00:00:00.99999 0000-00-00 23:59:59.999999 0000-00-00 23:59:59.99999 0000-00-01 00:00:00.999999 0000-00-01 00:00:00.99999 0000-00-01 23:59:59.999999 0000-00-01 23:59:59.99999 0000-00-31 23:59:59.999999 0000-00-31 23:59:59.99999 0000-01-01 00:00:00.999999 0000-01-01 00:00:00.99999 0000-01-01 23:59:59.999999 0000-01-01 23:59:59.99999 0000-01-31 23:59:59.999999 0000-01-31 23:59:59.99999 0000-02-28 23:59:59.999999 0000-02-28 23:59:59.99999 0000-12-31 23:59:59.999999 0000-12-31 23:59:59.99999 0001-01-01 00:00:00.999999 0001-01-01 00:00:00.99999 0001-02-28 23:59:59.999999 0001-02-28 23:59:59.99999 0001-12-31 23:59:59.999999 0001-12-31 23:59:59.99999 0004-02-28 23:59:59.999999 0004-02-28 23:59:59.99999 0004-02-29 23:59:59.999999 0004-02-29 23:59:59.99999 2000-02-29 23:59:59.999999 2000-02-29 23:59:59.99999 2000-12-31 23:59:59.999999 2000-12-31 23:59:59.99999 9999-12-31 23:59:59.999999 9999-12-31 23:59:59.99999 SELECT a, TRUNCATE(a,6) FROM t1; a TRUNCATE(a,6) 0000-00-00 00:00:00.999999 0000-00-00 00:00:00.999999 0000-00-00 23:59:59.999999 0000-00-00 23:59:59.999999 0000-00-01 00:00:00.999999 0000-00-01 00:00:00.999999 0000-00-01 23:59:59.999999 0000-00-01 23:59:59.999999 0000-00-31 23:59:59.999999 0000-00-31 23:59:59.999999 0000-01-01 00:00:00.999999 0000-01-01 00:00:00.999999 0000-01-01 23:59:59.999999 0000-01-01 23:59:59.999999 0000-01-31 23:59:59.999999 0000-01-31 23:59:59.999999 0000-02-28 23:59:59.999999 0000-02-28 23:59:59.999999 0000-12-31 23:59:59.999999 0000-12-31 23:59:59.999999 0001-01-01 00:00:00.999999 0001-01-01 00:00:00.999999 0001-02-28 23:59:59.999999 0001-02-28 23:59:59.999999 0001-12-31 23:59:59.999999 0001-12-31 23:59:59.999999 0004-02-28 23:59:59.999999 0004-02-28 23:59:59.999999 0004-02-29 23:59:59.999999 0004-02-29 23:59:59.999999 2000-02-29 23:59:59.999999 2000-02-29 23:59:59.999999 2000-12-31 23:59:59.999999 2000-12-31 23:59:59.999999 9999-12-31 23:59:59.999999 9999-12-31 23:59:59.999999 SELECT a, TRUNCATE(a,7) FROM t1; a TRUNCATE(a,7) 0000-00-00 00:00:00.999999 0000-00-00 00:00:00.999999 0000-00-00 23:59:59.999999 0000-00-00 23:59:59.999999 0000-00-01 00:00:00.999999 0000-00-01 00:00:00.999999 0000-00-01 23:59:59.999999 0000-00-01 23:59:59.999999 0000-00-31 23:59:59.999999 0000-00-31 23:59:59.999999 0000-01-01 00:00:00.999999 0000-01-01 00:00:00.999999 0000-01-01 23:59:59.999999 0000-01-01 23:59:59.999999 0000-01-31 23:59:59.999999 0000-01-31 23:59:59.999999 0000-02-28 23:59:59.999999 0000-02-28 23:59:59.999999 0000-12-31 23:59:59.999999 0000-12-31 23:59:59.999999 0001-01-01 00:00:00.999999 0001-01-01 00:00:00.999999 0001-02-28 23:59:59.999999 0001-02-28 23:59:59.999999 0001-12-31 23:59:59.999999 0001-12-31 23:59:59.999999 0004-02-28 23:59:59.999999 0004-02-28 23:59:59.999999 0004-02-29 23:59:59.999999 0004-02-29 23:59:59.999999 2000-02-29 23:59:59.999999 2000-02-29 23:59:59.999999 2000-12-31 23:59:59.999999 2000-12-31 23:59:59.999999 9999-12-31 23:59:59.999999 9999-12-31 23:59:59.999999 SELECT a, TRUNCATE(a,-1) FROM t1; a TRUNCATE(a,-1) 0000-00-00 00:00:00.999999 0000-00-00 00:00:00 0000-00-00 23:59:59.999999 0000-00-00 23:59:59 0000-00-01 00:00:00.999999 0000-00-01 00:00:00 0000-00-01 23:59:59.999999 0000-00-01 23:59:59 0000-00-31 23:59:59.999999 0000-00-31 23:59:59 0000-01-01 00:00:00.999999 0000-01-01 00:00:00 0000-01-01 23:59:59.999999 0000-01-01 23:59:59 0000-01-31 23:59:59.999999 0000-01-31 23:59:59 0000-02-28 23:59:59.999999 0000-02-28 23:59:59 0000-12-31 23:59:59.999999 0000-12-31 23:59:59 0001-01-01 00:00:00.999999 0001-01-01 00:00:00 0001-02-28 23:59:59.999999 0001-02-28 23:59:59 0001-12-31 23:59:59.999999 0001-12-31 23:59:59 0004-02-28 23:59:59.999999 0004-02-28 23:59:59 0004-02-29 23:59:59.999999 0004-02-29 23:59:59 2000-02-29 23:59:59.999999 2000-02-29 23:59:59 2000-12-31 23:59:59.999999 2000-12-31 23:59:59 9999-12-31 23:59:59.999999 9999-12-31 23:59:59 SELECT a, TRUNCATE(a,-6) FROM t1; a TRUNCATE(a,-6) 0000-00-00 00:00:00.999999 0000-00-00 00:00:00 0000-00-00 23:59:59.999999 0000-00-00 23:59:59 0000-00-01 00:00:00.999999 0000-00-01 00:00:00 0000-00-01 23:59:59.999999 0000-00-01 23:59:59 0000-00-31 23:59:59.999999 0000-00-31 23:59:59 0000-01-01 00:00:00.999999 0000-01-01 00:00:00 0000-01-01 23:59:59.999999 0000-01-01 23:59:59 0000-01-31 23:59:59.999999 0000-01-31 23:59:59 0000-02-28 23:59:59.999999 0000-02-28 23:59:59 0000-12-31 23:59:59.999999 0000-12-31 23:59:59 0001-01-01 00:00:00.999999 0001-01-01 00:00:00 0001-02-28 23:59:59.999999 0001-02-28 23:59:59 0001-12-31 23:59:59.999999 0001-12-31 23:59:59 0004-02-28 23:59:59.999999 0004-02-28 23:59:59 0004-02-29 23:59:59.999999 0004-02-29 23:59:59 2000-02-29 23:59:59.999999 2000-02-29 23:59:59 2000-12-31 23:59:59.999999 2000-12-31 23:59:59 9999-12-31 23:59:59.999999 9999-12-31 23:59:59 SELECT a, ROUND(a) FROM t1; a ROUND(a) 0000-00-00 00:00:00.999999 0000-00-00 00:00:01 0000-00-00 23:59:59.999999 NULL 0000-00-01 00:00:00.999999 0000-00-01 00:00:01 0000-00-01 23:59:59.999999 NULL 0000-00-31 23:59:59.999999 NULL 0000-01-01 00:00:00.999999 0000-01-01 00:00:01 0000-01-01 23:59:59.999999 NULL 0000-01-31 23:59:59.999999 NULL 0000-02-28 23:59:59.999999 NULL 0000-12-31 23:59:59.999999 NULL 0001-01-01 00:00:00.999999 0001-01-01 00:00:01 0001-02-28 23:59:59.999999 0001-03-01 00:00:00 0001-12-31 23:59:59.999999 0002-01-01 00:00:00 0004-02-28 23:59:59.999999 0004-02-29 00:00:00 0004-02-29 23:59:59.999999 0004-03-01 00:00:00 2000-02-29 23:59:59.999999 2000-03-01 00:00:00 2000-12-31 23:59:59.999999 2001-01-01 00:00:00 9999-12-31 23:59:59.999999 9999-12-31 23:59:59 Warnings: Warning 1411 Incorrect date value: '0000-00-00' for function round(datetime) Warning 1411 Incorrect date value: '0000-00-01' for function round(datetime) Warning 1411 Incorrect date value: '0000-00-31' for function round(datetime) Warning 1411 Incorrect date value: '0000-01-01' for function round(datetime) Warning 1411 Incorrect date value: '0000-01-31' for function round(datetime) Warning 1411 Incorrect date value: '0000-02-28' for function round(datetime) Warning 1411 Incorrect date value: '0000-12-31' for function round(datetime) SELECT a, ROUND(a,0) FROM t1; a ROUND(a,0) 0000-00-00 00:00:00.999999 0000-00-00 00:00:01 0000-00-00 23:59:59.999999 NULL 0000-00-01 00:00:00.999999 0000-00-01 00:00:01 0000-00-01 23:59:59.999999 NULL 0000-00-31 23:59:59.999999 NULL 0000-01-01 00:00:00.999999 0000-01-01 00:00:01 0000-01-01 23:59:59.999999 NULL 0000-01-31 23:59:59.999999 NULL 0000-02-28 23:59:59.999999 NULL 0000-12-31 23:59:59.999999 NULL 0001-01-01 00:00:00.999999 0001-01-01 00:00:01 0001-02-28 23:59:59.999999 0001-03-01 00:00:00 0001-12-31 23:59:59.999999 0002-01-01 00:00:00 0004-02-28 23:59:59.999999 0004-02-29 00:00:00 0004-02-29 23:59:59.999999 0004-03-01 00:00:00 2000-02-29 23:59:59.999999 2000-03-01 00:00:00 2000-12-31 23:59:59.999999 2001-01-01 00:00:00 9999-12-31 23:59:59.999999 9999-12-31 23:59:59 Warnings: Warning 1411 Incorrect date value: '0000-00-00' for function round(datetime) Warning 1411 Incorrect date value: '0000-00-01' for function round(datetime) Warning 1411 Incorrect date value: '0000-00-31' for function round(datetime) Warning 1411 Incorrect date value: '0000-01-01' for function round(datetime) Warning 1411 Incorrect date value: '0000-01-31' for function round(datetime) Warning 1411 Incorrect date value: '0000-02-28' for function round(datetime) Warning 1411 Incorrect date value: '0000-12-31' for function round(datetime) SELECT a, ROUND(a,1) FROM t1; a ROUND(a,1) 0000-00-00 00:00:00.999999 0000-00-00 00:00:01.0 0000-00-00 23:59:59.999999 NULL 0000-00-01 00:00:00.999999 0000-00-01 00:00:01.0 0000-00-01 23:59:59.999999 NULL 0000-00-31 23:59:59.999999 NULL 0000-01-01 00:00:00.999999 0000-01-01 00:00:01.0 0000-01-01 23:59:59.999999 NULL 0000-01-31 23:59:59.999999 NULL 0000-02-28 23:59:59.999999 NULL 0000-12-31 23:59:59.999999 NULL 0001-01-01 00:00:00.999999 0001-01-01 00:00:01.0 0001-02-28 23:59:59.999999 0001-03-01 00:00:00.0 0001-12-31 23:59:59.999999 0002-01-01 00:00:00.0 0004-02-28 23:59:59.999999 0004-02-29 00:00:00.0 0004-02-29 23:59:59.999999 0004-03-01 00:00:00.0 2000-02-29 23:59:59.999999 2000-03-01 00:00:00.0 2000-12-31 23:59:59.999999 2001-01-01 00:00:00.0 9999-12-31 23:59:59.999999 9999-12-31 23:59:59.9 Warnings: Warning 1411 Incorrect date value: '0000-00-00' for function round(datetime) Warning 1411 Incorrect date value: '0000-00-01' for function round(datetime) Warning 1411 Incorrect date value: '0000-00-31' for function round(datetime) Warning 1411 Incorrect date value: '0000-01-01' for function round(datetime) Warning 1411 Incorrect date value: '0000-01-31' for function round(datetime) Warning 1411 Incorrect date value: '0000-02-28' for function round(datetime) Warning 1411 Incorrect date value: '0000-12-31' for function round(datetime) SELECT a, ROUND(a,2) FROM t1; a ROUND(a,2) 0000-00-00 00:00:00.999999 0000-00-00 00:00:01.00 0000-00-00 23:59:59.999999 NULL 0000-00-01 00:00:00.999999 0000-00-01 00:00:01.00 0000-00-01 23:59:59.999999 NULL 0000-00-31 23:59:59.999999 NULL 0000-01-01 00:00:00.999999 0000-01-01 00:00:01.00 0000-01-01 23:59:59.999999 NULL 0000-01-31 23:59:59.999999 NULL 0000-02-28 23:59:59.999999 NULL 0000-12-31 23:59:59.999999 NULL 0001-01-01 00:00:00.999999 0001-01-01 00:00:01.00 0001-02-28 23:59:59.999999 0001-03-01 00:00:00.00 0001-12-31 23:59:59.999999 0002-01-01 00:00:00.00 0004-02-28 23:59:59.999999 0004-02-29 00:00:00.00 0004-02-29 23:59:59.999999 0004-03-01 00:00:00.00 2000-02-29 23:59:59.999999 2000-03-01 00:00:00.00 2000-12-31 23:59:59.999999 2001-01-01 00:00:00.00 9999-12-31 23:59:59.999999 9999-12-31 23:59:59.99 Warnings: Warning 1411 Incorrect date value: '0000-00-00' for function round(datetime) Warning 1411 Incorrect date value: '0000-00-01' for function round(datetime) Warning 1411 Incorrect date value: '0000-00-31' for function round(datetime) Warning 1411 Incorrect date value: '0000-01-01' for function round(datetime) Warning 1411 Incorrect date value: '0000-01-31' for function round(datetime) Warning 1411 Incorrect date value: '0000-02-28' for function round(datetime) Warning 1411 Incorrect date value: '0000-12-31' for function round(datetime) SELECT a, ROUND(a,3) FROM t1; a ROUND(a,3) 0000-00-00 00:00:00.999999 0000-00-00 00:00:01.000 0000-00-00 23:59:59.999999 NULL 0000-00-01 00:00:00.999999 0000-00-01 00:00:01.000 0000-00-01 23:59:59.999999 NULL 0000-00-31 23:59:59.999999 NULL 0000-01-01 00:00:00.999999 0000-01-01 00:00:01.000 0000-01-01 23:59:59.999999 NULL 0000-01-31 23:59:59.999999 NULL 0000-02-28 23:59:59.999999 NULL 0000-12-31 23:59:59.999999 NULL 0001-01-01 00:00:00.999999 0001-01-01 00:00:01.000 0001-02-28 23:59:59.999999 0001-03-01 00:00:00.000 0001-12-31 23:59:59.999999 0002-01-01 00:00:00.000 0004-02-28 23:59:59.999999 0004-02-29 00:00:00.000 0004-02-29 23:59:59.999999 0004-03-01 00:00:00.000 2000-02-29 23:59:59.999999 2000-03-01 00:00:00.000 2000-12-31 23:59:59.999999 2001-01-01 00:00:00.000 9999-12-31 23:59:59.999999 9999-12-31 23:59:59.999 Warnings: Warning 1411 Incorrect date value: '0000-00-00' for function round(datetime) Warning 1411 Incorrect date value: '0000-00-01' for function round(datetime) Warning 1411 Incorrect date value: '0000-00-31' for function round(datetime) Warning 1411 Incorrect date value: '0000-01-01' for function round(datetime) Warning 1411 Incorrect date value: '0000-01-31' for function round(datetime) Warning 1411 Incorrect date value: '0000-02-28' for function round(datetime) Warning 1411 Incorrect date value: '0000-12-31' for function round(datetime) SELECT a, ROUND(a,4) FROM t1; a ROUND(a,4) 0000-00-00 00:00:00.999999 0000-00-00 00:00:01.0000 0000-00-00 23:59:59.999999 NULL 0000-00-01 00:00:00.999999 0000-00-01 00:00:01.0000 0000-00-01 23:59:59.999999 NULL 0000-00-31 23:59:59.999999 NULL 0000-01-01 00:00:00.999999 0000-01-01 00:00:01.0000 0000-01-01 23:59:59.999999 NULL 0000-01-31 23:59:59.999999 NULL 0000-02-28 23:59:59.999999 NULL 0000-12-31 23:59:59.999999 NULL 0001-01-01 00:00:00.999999 0001-01-01 00:00:01.0000 0001-02-28 23:59:59.999999 0001-03-01 00:00:00.0000 0001-12-31 23:59:59.999999 0002-01-01 00:00:00.0000 0004-02-28 23:59:59.999999 0004-02-29 00:00:00.0000 0004-02-29 23:59:59.999999 0004-03-01 00:00:00.0000 2000-02-29 23:59:59.999999 2000-03-01 00:00:00.0000 2000-12-31 23:59:59.999999 2001-01-01 00:00:00.0000 9999-12-31 23:59:59.999999 9999-12-31 23:59:59.9999 Warnings: Warning 1411 Incorrect date value: '0000-00-00' for function round(datetime) Warning 1411 Incorrect date value: '0000-00-01' for function round(datetime) Warning 1411 Incorrect date value: '0000-00-31' for function round(datetime) Warning 1411 Incorrect date value: '0000-01-01' for function round(datetime) Warning 1411 Incorrect date value: '0000-01-31' for function round(datetime) Warning 1411 Incorrect date value: '0000-02-28' for function round(datetime) Warning 1411 Incorrect date value: '0000-12-31' for function round(datetime) SELECT a, ROUND(a,5) FROM t1; a ROUND(a,5) 0000-00-00 00:00:00.999999 0000-00-00 00:00:01.00000 0000-00-00 23:59:59.999999 NULL 0000-00-01 00:00:00.999999 0000-00-01 00:00:01.00000 0000-00-01 23:59:59.999999 NULL 0000-00-31 23:59:59.999999 NULL 0000-01-01 00:00:00.999999 0000-01-01 00:00:01.00000 0000-01-01 23:59:59.999999 NULL 0000-01-31 23:59:59.999999 NULL 0000-02-28 23:59:59.999999 NULL 0000-12-31 23:59:59.999999 NULL 0001-01-01 00:00:00.999999 0001-01-01 00:00:01.00000 0001-02-28 23:59:59.999999 0001-03-01 00:00:00.00000 0001-12-31 23:59:59.999999 0002-01-01 00:00:00.00000 0004-02-28 23:59:59.999999 0004-02-29 00:00:00.00000 0004-02-29 23:59:59.999999 0004-03-01 00:00:00.00000 2000-02-29 23:59:59.999999 2000-03-01 00:00:00.00000 2000-12-31 23:59:59.999999 2001-01-01 00:00:00.00000 9999-12-31 23:59:59.999999 9999-12-31 23:59:59.99999 Warnings: Warning 1411 Incorrect date value: '0000-00-00' for function round(datetime) Warning 1411 Incorrect date value: '0000-00-01' for function round(datetime) Warning 1411 Incorrect date value: '0000-00-31' for function round(datetime) Warning 1411 Incorrect date value: '0000-01-01' for function round(datetime) Warning 1411 Incorrect date value: '0000-01-31' for function round(datetime) Warning 1411 Incorrect date value: '0000-02-28' for function round(datetime) Warning 1411 Incorrect date value: '0000-12-31' for function round(datetime) SELECT a, ROUND(a,6) FROM t1; a ROUND(a,6) 0000-00-00 00:00:00.999999 0000-00-00 00:00:00.999999 0000-00-00 23:59:59.999999 0000-00-00 23:59:59.999999 0000-00-01 00:00:00.999999 0000-00-01 00:00:00.999999 0000-00-01 23:59:59.999999 0000-00-01 23:59:59.999999 0000-00-31 23:59:59.999999 0000-00-31 23:59:59.999999 0000-01-01 00:00:00.999999 0000-01-01 00:00:00.999999 0000-01-01 23:59:59.999999 0000-01-01 23:59:59.999999 0000-01-31 23:59:59.999999 0000-01-31 23:59:59.999999 0000-02-28 23:59:59.999999 0000-02-28 23:59:59.999999 0000-12-31 23:59:59.999999 0000-12-31 23:59:59.999999 0001-01-01 00:00:00.999999 0001-01-01 00:00:00.999999 0001-02-28 23:59:59.999999 0001-02-28 23:59:59.999999 0001-12-31 23:59:59.999999 0001-12-31 23:59:59.999999 0004-02-28 23:59:59.999999 0004-02-28 23:59:59.999999 0004-02-29 23:59:59.999999 0004-02-29 23:59:59.999999 2000-02-29 23:59:59.999999 2000-02-29 23:59:59.999999 2000-12-31 23:59:59.999999 2000-12-31 23:59:59.999999 9999-12-31 23:59:59.999999 9999-12-31 23:59:59.999999 SELECT a, ROUND(a,7) FROM t1; a ROUND(a,7) 0000-00-00 00:00:00.999999 0000-00-00 00:00:00.999999 0000-00-00 23:59:59.999999 0000-00-00 23:59:59.999999 0000-00-01 00:00:00.999999 0000-00-01 00:00:00.999999 0000-00-01 23:59:59.999999 0000-00-01 23:59:59.999999 0000-00-31 23:59:59.999999 0000-00-31 23:59:59.999999 0000-01-01 00:00:00.999999 0000-01-01 00:00:00.999999 0000-01-01 23:59:59.999999 0000-01-01 23:59:59.999999 0000-01-31 23:59:59.999999 0000-01-31 23:59:59.999999 0000-02-28 23:59:59.999999 0000-02-28 23:59:59.999999 0000-12-31 23:59:59.999999 0000-12-31 23:59:59.999999 0001-01-01 00:00:00.999999 0001-01-01 00:00:00.999999 0001-02-28 23:59:59.999999 0001-02-28 23:59:59.999999 0001-12-31 23:59:59.999999 0001-12-31 23:59:59.999999 0004-02-28 23:59:59.999999 0004-02-28 23:59:59.999999 0004-02-29 23:59:59.999999 0004-02-29 23:59:59.999999 2000-02-29 23:59:59.999999 2000-02-29 23:59:59.999999 2000-12-31 23:59:59.999999 2000-12-31 23:59:59.999999 9999-12-31 23:59:59.999999 9999-12-31 23:59:59.999999 SELECT a, ROUND(a,-1) FROM t1; a ROUND(a,-1) 0000-00-00 00:00:00.999999 0000-00-00 00:00:01 0000-00-00 23:59:59.999999 NULL 0000-00-01 00:00:00.999999 0000-00-01 00:00:01 0000-00-01 23:59:59.999999 NULL 0000-00-31 23:59:59.999999 NULL 0000-01-01 00:00:00.999999 0000-01-01 00:00:01 0000-01-01 23:59:59.999999 NULL 0000-01-31 23:59:59.999999 NULL 0000-02-28 23:59:59.999999 NULL 0000-12-31 23:59:59.999999 NULL 0001-01-01 00:00:00.999999 0001-01-01 00:00:01 0001-02-28 23:59:59.999999 0001-03-01 00:00:00 0001-12-31 23:59:59.999999 0002-01-01 00:00:00 0004-02-28 23:59:59.999999 0004-02-29 00:00:00 0004-02-29 23:59:59.999999 0004-03-01 00:00:00 2000-02-29 23:59:59.999999 2000-03-01 00:00:00 2000-12-31 23:59:59.999999 2001-01-01 00:00:00 9999-12-31 23:59:59.999999 9999-12-31 23:59:59 Warnings: Warning 1411 Incorrect date value: '0000-00-00' for function round(datetime) Warning 1411 Incorrect date value: '0000-00-01' for function round(datetime) Warning 1411 Incorrect date value: '0000-00-31' for function round(datetime) Warning 1411 Incorrect date value: '0000-01-01' for function round(datetime) Warning 1411 Incorrect date value: '0000-01-31' for function round(datetime) Warning 1411 Incorrect date value: '0000-02-28' for function round(datetime) Warning 1411 Incorrect date value: '0000-12-31' for function round(datetime) SELECT a, ROUND(a,-6) FROM t1; a ROUND(a,-6) 0000-00-00 00:00:00.999999 0000-00-00 00:00:01 0000-00-00 23:59:59.999999 NULL 0000-00-01 00:00:00.999999 0000-00-01 00:00:01 0000-00-01 23:59:59.999999 NULL 0000-00-31 23:59:59.999999 NULL 0000-01-01 00:00:00.999999 0000-01-01 00:00:01 0000-01-01 23:59:59.999999 NULL 0000-01-31 23:59:59.999999 NULL 0000-02-28 23:59:59.999999 NULL 0000-12-31 23:59:59.999999 NULL 0001-01-01 00:00:00.999999 0001-01-01 00:00:01 0001-02-28 23:59:59.999999 0001-03-01 00:00:00 0001-12-31 23:59:59.999999 0002-01-01 00:00:00 0004-02-28 23:59:59.999999 0004-02-29 00:00:00 0004-02-29 23:59:59.999999 0004-03-01 00:00:00 2000-02-29 23:59:59.999999 2000-03-01 00:00:00 2000-12-31 23:59:59.999999 2001-01-01 00:00:00 9999-12-31 23:59:59.999999 9999-12-31 23:59:59 Warnings: Warning 1411 Incorrect date value: '0000-00-00' for function round(datetime) Warning 1411 Incorrect date value: '0000-00-01' for function round(datetime) Warning 1411 Incorrect date value: '0000-00-31' for function round(datetime) Warning 1411 Incorrect date value: '0000-01-01' for function round(datetime) Warning 1411 Incorrect date value: '0000-01-31' for function round(datetime) Warning 1411 Incorrect date value: '0000-02-28' for function round(datetime) Warning 1411 Incorrect date value: '0000-12-31' for function round(datetime) DROP TABLE t1; # # MDEV-20984 Possibly wrong result or Assertion `args[0]->type_handler()->mysql_timestamp_type() == MYSQL_TIMESTAMP_DATETIME' failed in Item_func_round::date_op # CREATE TABLE t1 (a DATETIME); INSERT INTO t1 VALUES ('1979-01-03 10:33:32'),('2012-12-12 12:12:12'); SELECT ROUND(a) AS f FROM t1 GROUP BY a WITH ROLLUP; f 1979-01-03 10:33:32 2012-12-12 12:12:12 NULL DROP TABLE t1; # # MDEV-23311 CEILING() and FLOOR() convert temporal input to numbers, unlike ROUND() and TRUNCATE() # CREATE TABLE t1 AS SELECT FLOOR(TIMESTAMP'2001-01-01 00:00:00.999999'), CEILING(TIMESTAMP'2001-01-01 00:00:00.999999'); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `FLOOR(TIMESTAMP'2001-01-01 00:00:00.999999')` datetime DEFAULT NULL, `CEILING(TIMESTAMP'2001-01-01 00:00:00.999999')` datetime DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE TABLE t1 (a DATETIME(6)); INSERT INTO t1 VALUES ('9999-12-31 23:59:59.999999'); INSERT INTO t1 VALUES ('9999-12-31 23:59:59.99999'); INSERT INTO t1 VALUES ('9999-12-31 23:59:59.9999'); INSERT INTO t1 VALUES ('9999-12-31 23:59:59.999'); INSERT INTO t1 VALUES ('9999-12-31 23:59:59.99'); INSERT INTO t1 VALUES ('9999-12-31 23:59:59.9'); INSERT INTO t1 VALUES ('9999-12-31 23:59:59.1'); INSERT INTO t1 VALUES ('9999-12-31 23:59:59.0'); INSERT INTO t1 VALUES ('9999-12-30 23:59:59.999999'); INSERT INTO t1 VALUES ('9999-12-30 23:59:59.99999'); INSERT INTO t1 VALUES ('9999-12-30 23:59:59.9999'); INSERT INTO t1 VALUES ('9999-12-30 23:59:59.999'); INSERT INTO t1 VALUES ('9999-12-30 23:59:59.99'); INSERT INTO t1 VALUES ('9999-12-30 23:59:59.9'); INSERT INTO t1 VALUES ('9999-12-30 23:59:59.1'); INSERT INTO t1 VALUES ('9999-12-30 23:59:59.0'); INSERT INTO t1 VALUES ('0999-12-31 23:59:59.999999'); INSERT INTO t1 VALUES ('0999-12-31 23:59:59.99999'); INSERT INTO t1 VALUES ('0999-12-31 23:59:59.9999'); INSERT INTO t1 VALUES ('0999-12-31 23:59:59.999'); INSERT INTO t1 VALUES ('0999-12-31 23:59:59.99'); INSERT INTO t1 VALUES ('0999-12-31 23:59:59.9'); INSERT INTO t1 VALUES ('0999-12-31 23:59:59.1'); INSERT INTO t1 VALUES ('0999-12-31 23:59:59.0'); INSERT INTO t1 VALUES ('0001-12-31 23:59:59.999999'); INSERT INTO t1 VALUES ('0001-12-31 23:59:59.99999'); INSERT INTO t1 VALUES ('0001-12-31 23:59:59.9999'); INSERT INTO t1 VALUES ('0001-12-31 23:59:59.999'); INSERT INTO t1 VALUES ('0001-12-31 23:59:59.99'); INSERT INTO t1 VALUES ('0001-12-31 23:59:59.9'); INSERT INTO t1 VALUES ('0001-12-31 23:59:59.1'); INSERT INTO t1 VALUES ('0001-12-31 23:59:59.0'); CREATE FUNCTION FLOOR_SP(a DATETIME(6)) RETURNS DATETIME BEGIN RETURN CASE WHEN EXTRACT(MICROSECOND FROM a)=0 THEN a ELSE TRUNCATE(a,0) END; END; $$ CREATE FUNCTION CEILING_SP(a DATETIME(6)) RETURNS DATETIME BEGIN RETURN CASE WHEN TRUNCATE(a,0)=TIMESTAMP'9999-12-31 23:59:59' THEN a WHEN EXTRACT(MICROSECOND FROM a)=0 THEN a ELSE TRUNCATE(a,0)+INTERVAL 1 SECOND END; END; $$ SELECT a, FLOOR(a), FLOOR_SP(a), FLOOR(a)=FLOOR_SP(a) FROM t1 ORDER BY a; a FLOOR(a) FLOOR_SP(a) FLOOR(a)=FLOOR_SP(a) 0001-12-31 23:59:59.000000 0001-12-31 23:59:59 0001-12-31 23:59:59 1 0001-12-31 23:59:59.100000 0001-12-31 23:59:59 0001-12-31 23:59:59 1 0001-12-31 23:59:59.900000 0001-12-31 23:59:59 0001-12-31 23:59:59 1 0001-12-31 23:59:59.990000 0001-12-31 23:59:59 0001-12-31 23:59:59 1 0001-12-31 23:59:59.999000 0001-12-31 23:59:59 0001-12-31 23:59:59 1 0001-12-31 23:59:59.999900 0001-12-31 23:59:59 0001-12-31 23:59:59 1 0001-12-31 23:59:59.999990 0001-12-31 23:59:59 0001-12-31 23:59:59 1 0001-12-31 23:59:59.999999 0001-12-31 23:59:59 0001-12-31 23:59:59 1 0999-12-31 23:59:59.000000 0999-12-31 23:59:59 0999-12-31 23:59:59 1 0999-12-31 23:59:59.100000 0999-12-31 23:59:59 0999-12-31 23:59:59 1 0999-12-31 23:59:59.900000 0999-12-31 23:59:59 0999-12-31 23:59:59 1 0999-12-31 23:59:59.990000 0999-12-31 23:59:59 0999-12-31 23:59:59 1 0999-12-31 23:59:59.999000 0999-12-31 23:59:59 0999-12-31 23:59:59 1 0999-12-31 23:59:59.999900 0999-12-31 23:59:59 0999-12-31 23:59:59 1 0999-12-31 23:59:59.999990 0999-12-31 23:59:59 0999-12-31 23:59:59 1 0999-12-31 23:59:59.999999 0999-12-31 23:59:59 0999-12-31 23:59:59 1 9999-12-30 23:59:59.000000 9999-12-30 23:59:59 9999-12-30 23:59:59 1 9999-12-30 23:59:59.100000 9999-12-30 23:59:59 9999-12-30 23:59:59 1 9999-12-30 23:59:59.900000 9999-12-30 23:59:59 9999-12-30 23:59:59 1 9999-12-30 23:59:59.990000 9999-12-30 23:59:59 9999-12-30 23:59:59 1 9999-12-30 23:59:59.999000 9999-12-30 23:59:59 9999-12-30 23:59:59 1 9999-12-30 23:59:59.999900 9999-12-30 23:59:59 9999-12-30 23:59:59 1 9999-12-30 23:59:59.999990 9999-12-30 23:59:59 9999-12-30 23:59:59 1 9999-12-30 23:59:59.999999 9999-12-30 23:59:59 9999-12-30 23:59:59 1 9999-12-31 23:59:59.000000 9999-12-31 23:59:59 9999-12-31 23:59:59 1 9999-12-31 23:59:59.100000 9999-12-31 23:59:59 9999-12-31 23:59:59 1 9999-12-31 23:59:59.900000 9999-12-31 23:59:59 9999-12-31 23:59:59 1 9999-12-31 23:59:59.990000 9999-12-31 23:59:59 9999-12-31 23:59:59 1 9999-12-31 23:59:59.999000 9999-12-31 23:59:59 9999-12-31 23:59:59 1 9999-12-31 23:59:59.999900 9999-12-31 23:59:59 9999-12-31 23:59:59 1 9999-12-31 23:59:59.999990 9999-12-31 23:59:59 9999-12-31 23:59:59 1 9999-12-31 23:59:59.999999 9999-12-31 23:59:59 9999-12-31 23:59:59 1 SELECT a, CEILING(a), CEILING_SP(a), CEILING(a)=CEILING_SP(a) FROM t1 ORDER BY a; a CEILING(a) CEILING_SP(a) CEILING(a)=CEILING_SP(a) 0001-12-31 23:59:59.000000 0001-12-31 23:59:59 0001-12-31 23:59:59 1 0001-12-31 23:59:59.100000 0002-01-01 00:00:00 0002-01-01 00:00:00 1 0001-12-31 23:59:59.900000 0002-01-01 00:00:00 0002-01-01 00:00:00 1 0001-12-31 23:59:59.990000 0002-01-01 00:00:00 0002-01-01 00:00:00 1 0001-12-31 23:59:59.999000 0002-01-01 00:00:00 0002-01-01 00:00:00 1 0001-12-31 23:59:59.999900 0002-01-01 00:00:00 0002-01-01 00:00:00 1 0001-12-31 23:59:59.999990 0002-01-01 00:00:00 0002-01-01 00:00:00 1 0001-12-31 23:59:59.999999 0002-01-01 00:00:00 0002-01-01 00:00:00 1 0999-12-31 23:59:59.000000 0999-12-31 23:59:59 0999-12-31 23:59:59 1 0999-12-31 23:59:59.100000 1000-01-01 00:00:00 1000-01-01 00:00:00 1 0999-12-31 23:59:59.900000 1000-01-01 00:00:00 1000-01-01 00:00:00 1 0999-12-31 23:59:59.990000 1000-01-01 00:00:00 1000-01-01 00:00:00 1 0999-12-31 23:59:59.999000 1000-01-01 00:00:00 1000-01-01 00:00:00 1 0999-12-31 23:59:59.999900 1000-01-01 00:00:00 1000-01-01 00:00:00 1 0999-12-31 23:59:59.999990 1000-01-01 00:00:00 1000-01-01 00:00:00 1 0999-12-31 23:59:59.999999 1000-01-01 00:00:00 1000-01-01 00:00:00 1 9999-12-30 23:59:59.000000 9999-12-30 23:59:59 9999-12-30 23:59:59 1 9999-12-30 23:59:59.100000 9999-12-31 00:00:00 9999-12-31 00:00:00 1 9999-12-30 23:59:59.900000 9999-12-31 00:00:00 9999-12-31 00:00:00 1 9999-12-30 23:59:59.990000 9999-12-31 00:00:00 9999-12-31 00:00:00 1 9999-12-30 23:59:59.999000 9999-12-31 00:00:00 9999-12-31 00:00:00 1 9999-12-30 23:59:59.999900 9999-12-31 00:00:00 9999-12-31 00:00:00 1 9999-12-30 23:59:59.999990 9999-12-31 00:00:00 9999-12-31 00:00:00 1 9999-12-30 23:59:59.999999 9999-12-31 00:00:00 9999-12-31 00:00:00 1 9999-12-31 23:59:59.000000 9999-12-31 23:59:59 9999-12-31 23:59:59 1 9999-12-31 23:59:59.100000 9999-12-31 23:59:59 9999-12-31 23:59:59 1 9999-12-31 23:59:59.900000 9999-12-31 23:59:59 9999-12-31 23:59:59 1 9999-12-31 23:59:59.990000 9999-12-31 23:59:59 9999-12-31 23:59:59 1 9999-12-31 23:59:59.999000 9999-12-31 23:59:59 9999-12-31 23:59:59 1 9999-12-31 23:59:59.999900 9999-12-31 23:59:59 9999-12-31 23:59:59 1 9999-12-31 23:59:59.999990 9999-12-31 23:59:59 9999-12-31 23:59:59 1 9999-12-31 23:59:59.999999 9999-12-31 23:59:59 9999-12-31 23:59:59 1 DROP FUNCTION FLOOR_SP; DROP FUNCTION CEILING_SP; DROP TABLE t1; CREATE TABLE t1 (a DATETIME(6)); INSERT INTO t1 VALUES ('0000-00-00 23:59:59.999999'); INSERT INTO t1 VALUES ('0000-00-01 23:59:59.999999'); INSERT INTO t1 VALUES ('0000-01-01 23:59:59.999999'); INSERT INTO t1 VALUES ('0001-00-00 23:59:59.999999'); SELECT a, FLOOR(a), CEILING(a) FROM t1; a FLOOR(a) CEILING(a) 0000-00-00 23:59:59.999999 0000-00-00 23:59:59 NULL 0000-00-01 23:59:59.999999 0000-00-01 23:59:59 NULL 0000-01-01 23:59:59.999999 0000-01-01 23:59:59 NULL 0001-00-00 23:59:59.999999 0001-00-00 23:59:59 NULL Warnings: Warning 1411 Incorrect date value: '0000-00-00' for function round(datetime) Warning 1411 Incorrect date value: '0000-00-01' for function round(datetime) Warning 1411 Incorrect date value: '0000-01-01' for function round(datetime) Warning 1411 Incorrect date value: '0001-00-00' for function round(datetime) DROP TABLE t1; SET sql_mode=ALLOW_INVALID_DATES; CREATE TABLE t1 (a DATETIME(6)); INSERT INTO t1 VALUES ('2001-02-28 23:59:59.999999'); INSERT INTO t1 VALUES ('2001-02-29 23:59:59.999999'); SELECT a, FLOOR(a), CEILING(a) FROM t1; a FLOOR(a) CEILING(a) 2001-02-28 23:59:59.999999 2001-02-28 23:59:59 2001-03-01 00:00:00 2001-02-29 23:59:59.999999 2001-02-29 23:59:59 NULL Warnings: Warning 1411 Incorrect date value: '2001-02-29' for function round(datetime) DROP TABLE t1; SET sql_mode=DEFAULT; # # End of 10.4 tests #