github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/function/func_datetime_year.result (about) 1 SELECT YEAR(date_add("2021-09-30", interval 200 day)) AS year; 2 year 3 2022 4 SELECT YEAR("10000-12-21") as year; 5 invalid argument parsedate, bad value 10000-12-21 6 SELECT YEAR("-1234-12-21") as year; 7 invalid argument parsedate, bad value -1234-12-21 8 SELECT YEAR("12415141412-12-21") as year; 9 invalid argument parsedate, bad value 12415141412-12-21 10 SELECT YEAR("0000-00-00") as year; 11 invalid argument parsedate, bad value 0000-00-00 12 SELECT YEAR("2049-14-34") as year; 13 invalid argument parsedate, bad value 2049-14-34 14 SELECT YEAR("2015-09") as year; 15 invalid argument parsedate, bad value 2015-09 16 SELECT YEAR(2015) as year; 17 invalid argument parsedate, bad value 2015 18 SELECT YEAR(1340124) as year; 19 year 20 134 21 SELECT YEAR(0.45133) as year; 22 invalid argument parsedate, bad value 0.45133 23 SELECT YEAR(1=1) as year; 24 invalid argument parsedate, bad value 1 25 SELECT YEAR('2015-09.30') as year; 26 invalid argument parsedate, bad value 2015-09.30 27 SELECT YEAR('2015-0') as year; 28 invalid argument parsedate, bad value 2015-0 29 SELECT YEAR('2015-09.30 12') as year; 30 invalid argument parsedate, bad value 2015-09.30 12 31 SELECT YEAR('2015-09-26 08:09:22') AS Year ; 32 Year 33 2015 34 SELECT YEAR("2015-09-10")- YEAR("2016-04-24"); 35 YEAR("2015-09-10")- YEAR("2016-04-24") 36 -1 37 SELECT YEAR("2015-09-10")+ YEAR("2016-04-24"); 38 YEAR("2015-09-10")+ YEAR("2016-04-24") 39 4031 40 SELECT YEAR("2015-09-10")*YEAR("2016-04-24"); 41 YEAR("2015-09-10")*YEAR("2016-04-24") 42 4062240 43 SELECT YEAR("2015-09-10")/YEAR("2016-04-24"); 44 YEAR("2015-09-10")/YEAR("2016-04-24") 45 0.9995 46 SELECT YEAR("2015-09-10")>YEAR("2016-04-24"); 47 YEAR("2015-09-10")>YEAR("2016-04-24") 48 false 49 SELECT YEAR("2015-09-10")<YEAR("2016-04-24"); 50 YEAR("2015-09-10")<YEAR("2016-04-24") 51 true 52 SELECT YEAR("2015-09-10")=YEAR("2016-04-24"); 53 YEAR("2015-09-10")=YEAR("2016-04-24") 54 false 55 SELECT YEAR("2015-09-10")<>YEAR("2016-04-24"); 56 YEAR("2015-09-10")<>YEAR("2016-04-24") 57 true 58 SELECT YEAR("2015-09-10")!=YEAR("2016-04-24"); 59 YEAR("2015-09-10")!=YEAR("2016-04-24") 60 true 61 SELECT YEAR(NULL) AS Year ; 62 Year 63 null 64 CREATE TABLE Product(Product_id INT, Product_name VARCHAR(100) NOT NULL,Buying_price DECIMAL(13, 2) NOT NULL,Selling_price DECIMAL(13, 2) NOT NULL,Selling_Date Date NOT NULL,PRIMARY KEY(Product_id)); 65 INSERT INTO Product(Product_name, Buying_price, Selling_price, Selling_Date) VALUES ('Audi Q8', 10000000.00, 15000000.00, '2018-01-26' ),('Volvo XC40', 2000000.00, 3000000.00, '2018-04-20' ),('Audi A6', 4000000.00, 5000000.00, '2018-07-25' ),('BMW X5', 5000500.00, 7006500.00, '2018-10-18' ),('Jaguar XF', 5000000, 7507000.00, '2019-01-27' ),('Mercedes-Benz C-Class', 4000000.00, 6000000.00, '2019-04-01' ),('Jaguar F-PACE', 5000000.00, 7000000.00, '2019-12-26' ),('Porsche Macan', 6500000.00, 8000000.00, '2020-04-16' ) ; 66 invalid input: invalid default value 67 SELECT * FROM Product; 68 Product_id Product_name Buying_price Selling_price Selling_Date 69 SELECT YEAR (Selling_Date) year, COUNT(Product_id) Product_Sold FROM Product GROUP BY YEAR (Selling_Date) ORDER BY YEAR (Selling_Date); 70 year Product_Sold 71 DROP TABLE Product; 72 drop table if exists t1; 73 create table t1(a INT, b date); 74 insert into t1 select year("2012-10-12"), "2012-10-12"; 75 insert into t1 select year("2004-04-24"), "2004-04-24"; 76 insert into t1 select year("2008-12-04"), "2008-12-04"; 77 select * from t1; 78 a b 79 2012 2012-10-12 80 2004 2004-04-24 81 2008 2008-12-04 82 drop table t1; 83 drop table if exists t1; 84 create table t1(a INT, b date); 85 insert into t1 values(1, "2012-10-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23"); 86 select * from t1 where year(b)="2012"; 87 a b 88 1 2012-10-12 89 4 2012-03-23 90 drop table t1; 91 drop table if exists t1; 92 drop table if exists t2; 93 create table t1(a INT, b date); 94 create table t2(a INT, b date); 95 insert into t1 values(1, "2012-10-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23"); 96 insert into t2 values(1, "2013-04-30"),(2, "1994-10-04"),(3, "2018-06-04"),(4, "2012-08-12"); 97 SELECT t1.a, t2.a FROM t1 JOIN t2 ON (year(t1.b) = year(t2.b)); 98 a a 99 4 4 100 1 4 101 drop table t1; 102 drop table t2; 103 drop table if exists t1; 104 create table t1(a INT, b date); 105 insert into t1 values(1, "2012-10-12"),(1, "2012-07-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23"); 106 select b from t1 group by b having year(b)="2012"; 107 b 108 2012-10-12 109 2012-07-12 110 2012-03-23 111 drop table t1; 112 drop table if exists t1; 113 create table t1(a INT, b date); 114 insert into t1 values(1, "2012-10-12"),(1, "2012-07-12"),(2, "2004-04-24"),(3, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23"); 115 select distinct year(b) from t1; 116 year(b) 117 2012 118 2004 119 2008 120 drop table t1; 121 CREATE TABLE t3(c1 DATE NOT NULL); 122 INSERT INTO t3 VALUES('2000-01-01'); 123 INSERT INTO t3 VALUES('1999-12-31'); 124 INSERT INTO t3 VALUES('2000-01-01'); 125 INSERT INTO t3 VALUES('2006-12-25'); 126 INSERT INTO t3 VALUES('2008-02-29'); 127 SELECT YEAR(c1) FROM t3; 128 YEAR(c1) 129 2000 130 1999 131 2000 132 2006 133 2008 134 DROP TABLE t3; 135 CREATE TABLE t3(c1 DATETIME NOT NULL); 136 INSERT INTO t3 VALUES('2000-01-01'); 137 INSERT INTO t3 VALUES('1999-12-31'); 138 INSERT INTO t3 VALUES('2000-01-01'); 139 INSERT INTO t3 VALUES('2006-12-25'); 140 INSERT INTO t3 VALUES('2008-02-29'); 141 SELECT YEAR(c1) FROM t3; 142 YEAR(c1) 143 2000 144 1999 145 2000 146 2006 147 2008 148 DROP TABLE t3; 149 CREATE TABLE t3(c1 TIMESTAMP NOT NULL); 150 INSERT INTO t3 VALUES('2000-01-01'); 151 INSERT INTO t3 VALUES('1999-12-31'); 152 INSERT INTO t3 VALUES('2000-01-01'); 153 INSERT INTO t3 VALUES('2006-12-25'); 154 INSERT INTO t3 VALUES('2008-02-29'); 155 SELECT YEAR(c1) FROM t3; 156 YEAR(c1) 157 2000 158 1999 159 2000 160 2006 161 2008 162 DROP TABLE t3;