github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/function/func_datetime_date.result (about) 1 SELECT DATE(0.0124); 2 invalid argument operator cast, bad value [DECIMAL128 DATE] 3 SELECT DATE("2112123"); 4 Data truncation: data out of range: data type date, '2112123' 5 SELECT DATE(1231241.4513); 6 invalid argument operator cast, bad value [DECIMAL128 DATE] 7 SELECT DATE("2017-06-15"); 8 DATE("2017-06-15") 9 2017-06-15 10 SELECT DATE("2017-06-15 09:34:21"); 11 DATE("2017-06-15 09:34:21") 12 2017-06-15 13 SELECT DATE("The date is 2017-06-15"); 14 Data truncation: data out of range: data type date, 'The date is 2017-06-15' 15 SELECT DATE('2008-05-17 11:31:31') as required_DATE; 16 required_DATE 17 2008-05-17 18 select coalesce(date(NULL)), coalesce(cast(NULL as DATE)); 19 coalesce(date(NULL)) coalesce(cast(NULL as DATE)) 20 null null 21 select date(NULL); 22 date(NULL) 23 null 24 CREATE TABLE t1 (d1 datetime); 25 INSERT INTO t1(d1) VALUES ('2007-07-19 08:30:00'), (NULL), 26 ('2007-07-19 08:34:00'), (NULL), ('2007-07-19 08:36:00'); 27 SELECT cast(date(d1) as signed) FROM t1; 28 cast(date(d1) as signed) 29 13713 30 null 31 13713 32 null 33 13713 34 SELECT d1 % 7 FROM t1; 35 d1 % 7 36 3 37 null 38 5 39 null 40 6 41 drop table t1; 42 CREATE TABLE t1 (d1 datetime); 43 INSERT INTO t1(d1) VALUES ('2007-07-19 08:30:00'), (NULL), 44 ('2007-07-19 08:34:00'), (NULL), ('2007-07-19 08:36:00'); 45 SELECT sum(date(d1)) FROM t1; 46 invalid argument aggregate function sum, bad value [DATE] 47 drop table t1; 48 select date("1997-12-31 23:59:59.000001"); 49 date("1997-12-31 23:59:59.000001") 50 1997-12-31 51 select date("1997-13-31 23:59:59.000001"); 52 Data truncation: data out of range: data type date, '1997-13-31 23:59:59.000001' 53 SELECT DATE(20110512154559.6 + 0e0); 54 invalid argument operator cast, bad value [DOUBLE DATE] 55 SELECT DATE(concat_ws('a', 0)); 56 Data truncation: data out of range: data type date, '0' 57 CREATE TABLE t1 (a timestamp); 58 INSERT INTO t1 VALUES ("2020-12-31 12:01:32"); 59 SELECT DATE(MIN(a)) FROM t1; 60 DATE(MIN(a)) 61 2020-12-31 62 DROP TABLE t1; 63 CREATE TABLE t1 64 (first_usage DATE, last_recharge DATETIME, life_time SMALLINT(4) UNSIGNED); 65 INSERT INTO t1 VALUES ('2011-04-27', null, 900); 66 SELECT 67 DATE_ADD(coalesce(last_recharge, first_usage), INTERVAL life_time DAY ) as dt, 68 DATE_ADD(coalesce(last_recharge, first_usage), INTERVAL life_time DAY ) < 69 DATE('2011-04-28') as exp FROM t1; 70 dt exp 71 2013-10-13 00:00:00 false 72 DROP TABLE t1; 73 SELECT DATE(20110512154559.616), DATE(FLOOR(20110512154559.616)); 74 invalid argument operator cast, bad value [DECIMAL128 DATE] 75 DROP TABLE IF EXISTS t3; 76 CREATE TABLE t3(c1 DATE NOT NULL); 77 INSERT INTO t3 VALUES('2000-01-01'); 78 INSERT INTO t3 VALUES('1999-12-31'); 79 INSERT INTO t3 VALUES('2000-01-01'); 80 INSERT INTO t3 VALUES('2006-12-25'); 81 INSERT INTO t3 VALUES('2008-02-29'); 82 SELECT DATE(c1) FROM t3; 83 DATE(c1) 84 2000-01-01 85 1999-12-31 86 2000-01-01 87 2006-12-25 88 2008-02-29 89 DROP TABLE t3; 90 CREATE TABLE t3(c1 DATETIME NOT NULL); 91 INSERT INTO t3 VALUES('2000-01-01'); 92 INSERT INTO t3 VALUES('1999-12-31'); 93 INSERT INTO t3 VALUES('2000-01-01'); 94 INSERT INTO t3 VALUES('2006-12-25'); 95 INSERT INTO t3 VALUES('2008-02-29'); 96 SELECT DATE(c1) FROM t3; 97 DATE(c1) 98 2000-01-01 99 1999-12-31 100 2000-01-01 101 2006-12-25 102 2008-02-29 103 DROP TABLE t3; 104 CREATE TABLE t3(c1 TIMESTAMP NOT NULL); 105 INSERT INTO t3 VALUES('2000-01-01'); 106 INSERT INTO t3 VALUES('1999-12-31'); 107 INSERT INTO t3 VALUES('2000-01-01'); 108 INSERT INTO t3 VALUES('2006-12-25'); 109 INSERT INTO t3 VALUES('2008-02-29'); 110 SELECT DATE(c1) FROM t3; 111 DATE(c1) 112 2000-01-01 113 1999-12-31 114 2000-01-01 115 2006-12-25 116 2008-02-29 117 DROP TABLE t3; 118 CREATE TABLE t3(c1 DATE NOT NULL); 119 INSERT INTO t3 VALUES('2000-01-01'); 120 INSERT INTO t3 VALUES('1999-12-31'); 121 INSERT INTO t3 VALUES('2000-01-01'); 122 INSERT INTO t3 VALUES('2006-12-25'); 123 INSERT INTO t3 VALUES('2008-02-29'); 124 SELECT DAYOFYEAR(c1) FROM t3; 125 DAYOFYEAR(c1) 126 1 127 365 128 1 129 359 130 60 131 DROP TABLE t3; 132 CREATE TABLE t3(c1 DATETIME NOT NULL); 133 INSERT INTO t3 VALUES('2000-01-01'); 134 INSERT INTO t3 VALUES('1999-12-31'); 135 INSERT INTO t3 VALUES('2000-01-01'); 136 INSERT INTO t3 VALUES('2006-12-25'); 137 INSERT INTO t3 VALUES('2008-02-29'); 138 SELECT DAYOFYEAR(c1) FROM t3; 139 DAYOFYEAR(c1) 140 1 141 365 142 1 143 359 144 60 145 DROP TABLE t3; 146 CREATE TABLE t3(c1 TIMESTAMP NOT NULL); 147 INSERT INTO t3 VALUES('2000-01-01'); 148 INSERT INTO t3 VALUES('1999-12-31'); 149 INSERT INTO t3 VALUES('2000-01-01'); 150 INSERT INTO t3 VALUES('2006-12-25'); 151 INSERT INTO t3 VALUES('2008-02-29'); 152 SELECT DAYOFYEAR(c1) FROM t3; 153 DAYOFYEAR(c1) 154 1 155 365 156 1 157 359 158 60 159 DROP TABLE t3; 160 CREATE TABLE t3(c1 DATE NOT NULL); 161 INSERT INTO t3 VALUES('2000-01-01'); 162 INSERT INTO t3 VALUES('1999-12-31'); 163 INSERT INTO t3 VALUES('2000-01-01'); 164 INSERT INTO t3 VALUES('2006-12-25'); 165 INSERT INTO t3 VALUES('2008-02-29'); 166 SELECT MONTH(c1) FROM t3; 167 MONTH(c1) 168 1 169 12 170 1 171 12 172 2 173 DROP TABLE t3; 174 CREATE TABLE t3(c1 DATETIME NOT NULL); 175 INSERT INTO t3 VALUES('2000-01-01'); 176 INSERT INTO t3 VALUES('1999-12-31'); 177 INSERT INTO t3 VALUES('2000-01-01'); 178 INSERT INTO t3 VALUES('2006-12-25'); 179 INSERT INTO t3 VALUES('2008-02-29'); 180 SELECT MONTH(c1) FROM t3; 181 MONTH(c1) 182 1 183 12 184 1 185 12 186 2 187 DROP TABLE t3; 188 CREATE TABLE t3(c1 TIMESTAMP NOT NULL); 189 INSERT INTO t3 VALUES('2000-01-01'); 190 INSERT INTO t3 VALUES('1999-12-31'); 191 INSERT INTO t3 VALUES('2000-01-01'); 192 INSERT INTO t3 VALUES('2006-12-25'); 193 INSERT INTO t3 VALUES('2008-02-29'); 194 SELECT MONTH(c1) FROM t3; 195 MONTH(c1) 196 1 197 12 198 1 199 12 200 2 201 DROP TABLE t3; 202 CREATE TABLE t3(c1 DATE NOT NULL); 203 INSERT INTO t3 VALUES('2000-01-01'); 204 INSERT INTO t3 VALUES('1999-12-31'); 205 INSERT INTO t3 VALUES('2000-01-01'); 206 INSERT INTO t3 VALUES('2006-12-25'); 207 INSERT INTO t3 VALUES('2008-02-29'); 208 SELECT WEEKDAY(c1) FROM t3; 209 WEEKDAY(c1) 210 5 211 4 212 5 213 0 214 4 215 DROP TABLE t3; 216 CREATE TABLE t3(c1 DATETIME NOT NULL); 217 INSERT INTO t3 VALUES('2000-01-01'); 218 INSERT INTO t3 VALUES('1999-12-31'); 219 INSERT INTO t3 VALUES('2000-01-01'); 220 INSERT INTO t3 VALUES('2006-12-25'); 221 INSERT INTO t3 VALUES('2008-02-29'); 222 SELECT WEEKDAY(c1) FROM t3; 223 WEEKDAY(c1) 224 5 225 4 226 5 227 0 228 4 229 DROP TABLE t3; 230 CREATE TABLE t3(c1 TIMESTAMP NOT NULL); 231 INSERT INTO t3 VALUES('2000-01-01'); 232 INSERT INTO t3 VALUES('1999-12-31'); 233 INSERT INTO t3 VALUES('2000-01-01'); 234 INSERT INTO t3 VALUES('2006-12-25'); 235 INSERT INTO t3 VALUES('2008-02-29'); 236 SELECT WEEKDAY(c1) FROM t3; 237 WEEKDAY(c1) 238 5 239 4 240 5 241 0 242 4 243 DROP TABLE t3; 244 CREATE TABLE t3(c1 DATE NOT NULL); 245 INSERT INTO t3 VALUES('2000-01-01'); 246 INSERT INTO t3 VALUES('1999-12-31'); 247 INSERT INTO t3 VALUES('2000-01-01'); 248 INSERT INTO t3 VALUES('2006-12-25'); 249 INSERT INTO t3 VALUES('2008-02-29'); 250 SELECT YEAR(c1) FROM t3; 251 YEAR(c1) 252 2000 253 1999 254 2000 255 2006 256 2008 257 DROP TABLE t3; 258 CREATE TABLE t3(c1 DATETIME NOT NULL); 259 INSERT INTO t3 VALUES('2000-01-01'); 260 INSERT INTO t3 VALUES('1999-12-31'); 261 INSERT INTO t3 VALUES('2000-01-01'); 262 INSERT INTO t3 VALUES('2006-12-25'); 263 INSERT INTO t3 VALUES('2008-02-29'); 264 SELECT YEAR(c1) FROM t3; 265 YEAR(c1) 266 2000 267 1999 268 2000 269 2006 270 2008 271 DROP TABLE t3; 272 CREATE TABLE t3(c1 TIMESTAMP NOT NULL); 273 INSERT INTO t3 VALUES('2000-01-01'); 274 INSERT INTO t3 VALUES('1999-12-31'); 275 INSERT INTO t3 VALUES('2000-01-01'); 276 INSERT INTO t3 VALUES('2006-12-25'); 277 INSERT INTO t3 VALUES('2008-02-29'); 278 SELECT YEAR(c1) FROM t3; 279 YEAR(c1) 280 2000 281 1999 282 2000 283 2006 284 2008 285 DROP TABLE t3; 286 SELECT date("2022-12-22 02:34:23") = date("2022-12-22 03:34:23"); 287 date("2022-12-22 02:34:23") = date("2022-12-22 03:34:23") 288 true 289 drop table if exists t1; 290 create table t1(a date); 291 insert into t1 SELECT DATE("2017-06-15 09:34:21"); 292 insert into t1 SELECT DATE("2019-06-25 10:12:21"); 293 insert into t1 SELECT DATE("2019-06-25 18:20:49"); 294 select distinct a from t1; 295 a 296 2017-06-15 297 2019-06-25 298 drop table t1; 299 drop table if exists t1; 300 create table t1(a INT, b datetime); 301 insert into t1 values(1, "2017-06-15 09:34:21"),(1, "2019-06-25 10:12:21"),(2, "2019-06-25 18:20:49"),(3, "2019-06-25 18:20:49"); 302 select b from t1 group by b having date(b)>"2018-01-01"; 303 b 304 2019-06-25 10:12:21 305 2019-06-25 18:20:49 306 drop table t1; 307 drop table if exists t1; 308 create table t1(a INT, b date); 309 insert into t1 values(1, "2012-10-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23"); 310 select * from t1 where date(b)!="2012-10-12"; 311 a b 312 2 2004-04-24 313 3 2008-12-04 314 4 2012-03-23 315 drop table t1; 316 create table t1(a INT, b date); 317 create table t2(a INT, b date); 318 insert into t1 values(1, "2012-10-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23"); 319 insert into t2 values(1, "2013-04-30"),(2, "1994-10-04"),(3, "2018-06-04"),(4, "2012-10-12"); 320 SELECT t1.a, t2.a FROM t1 JOIN t2 ON (date(t1.b) = date(t2.b)); 321 a a 322 1 4 323 drop table t1; 324 drop table t2;