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