github.com/matrixorigin/matrixone@v1.2.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('2023-11-08 15:00') AS Year ; 35 Year 36 2023 37 SELECT YEAR("2015-09-10")- YEAR("2016-04-24"); 38 YEAR("2015-09-10")- YEAR("2016-04-24") 39 -1 40 SELECT YEAR("2015-09-10")+ YEAR("2016-04-24"); 41 YEAR("2015-09-10")+ YEAR("2016-04-24") 42 4031 43 SELECT YEAR("2015-09-10")*YEAR("2016-04-24"); 44 YEAR("2015-09-10")*YEAR("2016-04-24") 45 4062240 46 SELECT YEAR("2015-09-10")/YEAR("2016-04-24"); 47 YEAR("2015-09-10")/YEAR("2016-04-24") 48 0.9995 49 SELECT YEAR("2015-09-10")>YEAR("2016-04-24"); 50 YEAR("2015-09-10")>YEAR("2016-04-24") 51 false 52 SELECT YEAR("2015-09-10")<YEAR("2016-04-24"); 53 YEAR("2015-09-10")<YEAR("2016-04-24") 54 true 55 SELECT YEAR("2015-09-10")=YEAR("2016-04-24"); 56 YEAR("2015-09-10")=YEAR("2016-04-24") 57 false 58 SELECT YEAR("2015-09-10")<>YEAR("2016-04-24"); 59 YEAR("2015-09-10")<>YEAR("2016-04-24") 60 true 61 SELECT YEAR("2015-09-10")!=YEAR("2016-04-24"); 62 YEAR("2015-09-10")!=YEAR("2016-04-24") 63 true 64 SELECT YEAR(NULL) AS Year ; 65 Year 66 null 67 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)); 68 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' ) ; 69 invalid input: invalid default value for column 'product_id' 70 SELECT * FROM Product; 71 Product_id Product_name Buying_price Selling_price Selling_Date 72 SELECT YEAR (Selling_Date) year, COUNT(Product_id) Product_Sold FROM Product GROUP BY YEAR (Selling_Date) ORDER BY YEAR (Selling_Date); 73 year Product_Sold 74 DROP TABLE Product; 75 drop table if exists t1; 76 create table t1(a INT, b date); 77 insert into t1 select year("2012-10-12"), "2012-10-12"; 78 insert into t1 select year("2004-04-24"), "2004-04-24"; 79 insert into t1 select year("2008-12-04"), "2008-12-04"; 80 select * from t1; 81 a b 82 2012 2012-10-12 83 2004 2004-04-24 84 2008 2008-12-04 85 drop table t1; 86 drop table if exists t1; 87 create table t1(a INT, b date); 88 insert into t1 values(1, "2012-10-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23"); 89 select * from t1 where year(b)="2012"; 90 a b 91 1 2012-10-12 92 4 2012-03-23 93 drop table t1; 94 drop table if exists t1; 95 drop table if exists t2; 96 create table t1(a INT, b date); 97 create table t2(a INT, b date); 98 insert into t1 values(1, "2012-10-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23"); 99 insert into t2 values(1, "2013-04-30"),(2, "1994-10-04"),(3, "2018-06-04"),(4, "2012-08-12"); 100 SELECT t1.a, t2.a FROM t1 JOIN t2 ON (year(t1.b) = year(t2.b)); 101 a a 102 4 4 103 1 4 104 drop table t1; 105 drop table t2; 106 drop table if exists t1; 107 create table t1(a INT, b date); 108 insert into t1 values(1, "2012-10-12"),(1, "2012-07-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23"); 109 select b from t1 group by b having year(b)="2012"; 110 b 111 2012-10-12 112 2012-07-12 113 2012-03-23 114 drop table t1; 115 drop table if exists t1; 116 create table t1(a INT, b date); 117 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"); 118 select distinct year(b) from t1; 119 year(b) 120 2012 121 2004 122 2008 123 drop table t1; 124 CREATE TABLE t3(c1 DATE NOT NULL); 125 INSERT INTO t3 VALUES('2000-01-01'); 126 INSERT INTO t3 VALUES('1999-12-31'); 127 INSERT INTO t3 VALUES('2000-01-01'); 128 INSERT INTO t3 VALUES('2006-12-25'); 129 INSERT INTO t3 VALUES('2008-02-29'); 130 SELECT YEAR(c1) FROM t3; 131 YEAR(c1) 132 2000 133 1999 134 2000 135 2006 136 2008 137 DROP TABLE t3; 138 CREATE TABLE t3(c1 DATETIME NOT NULL); 139 INSERT INTO t3 VALUES('2000-01-01'); 140 INSERT INTO t3 VALUES('1999-12-31'); 141 INSERT INTO t3 VALUES('2000-01-01'); 142 INSERT INTO t3 VALUES('2006-12-25'); 143 INSERT INTO t3 VALUES('2008-02-29'); 144 SELECT YEAR(c1) FROM t3; 145 YEAR(c1) 146 2000 147 1999 148 2000 149 2006 150 2008 151 DROP TABLE t3; 152 CREATE TABLE t3(c1 TIMESTAMP NOT NULL); 153 INSERT INTO t3 VALUES('2000-01-01'); 154 INSERT INTO t3 VALUES('1999-12-31'); 155 INSERT INTO t3 VALUES('2000-01-01'); 156 INSERT INTO t3 VALUES('2006-12-25'); 157 INSERT INTO t3 VALUES('2008-02-29'); 158 SELECT YEAR(c1) FROM t3; 159 YEAR(c1) 160 2000 161 1999 162 2000 163 2006 164 2008 165 DROP TABLE t3;