github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_datetime_month.result (about) 1 SELECT MONTH(date_add("2021-09-30", interval 200 day)) AS MONTH; 2 MONTH 3 4 4 SELECT MONTH("10000-12-21") as month; 5 month 6 null 7 SELECT MONTH("-1234-12-21") as month; 8 month 9 null 10 SELECT MONTH("12415141412-12-21") as month; 11 month 12 null 13 SELECT MONTH("0000-00-00") as month; 14 month 15 null 16 SELECT MONTH("2049-14-34") as month; 17 month 18 null 19 SELECT MONTH("13-12") as month; 20 month 21 null 22 SELECT MONTH("2015-09") as MONTH; 23 MONTH 24 null 25 SELECT MONTH(201509) as MONTH; 26 month 27 null 28 SELECT MONTH(1340124) as MONTH; 29 month 30 1 31 SELECT MONTH(0.45133) as MONTH; 32 month 33 null 34 SELECT MONTH(10) as MONTH; 35 month 36 null 37 SELECT MONTH(1=1) as MONTH; 38 month 39 null 40 SELECT MONTH('2015-09-30') as MONTH; 41 MONTH 42 9 43 SELECT MONTH('2015-0') as MONTH; 44 MONTH 45 null 46 SELECT MONTH('2015-09.30 12') as MONTH; 47 MONTH 48 null 49 SELECT MONTH('2015-09-26 08:09:22') AS MONTH; 50 MONTH 51 9 52 SELECT MONTH("2015-09-10")- MONTH("2016-04-24"); 53 MONTH("2015-09-10")- MONTH("2016-04-24") 54 5 55 SELECT MONTH("2015-09-10")+ MONTH("2016-04-24"); 56 MONTH("2015-09-10")+ MONTH("2016-04-24") 57 13 58 SELECT MONTH("2015-09-10")*MONTH("2016-04-24"); 59 MONTH("2015-09-10")*MONTH("2016-04-24") 60 36 61 SELECT MONTH("2015-09-10")/MONTH("2016-04-24"); 62 MONTH("2015-09-10")/MONTH("2016-04-24") 63 2.2500 64 SELECT MONTH("2015-09-10")>MONTH("2016-04-24"); 65 month(2015-09-10) > month(2016-04-24) 66 true 67 SELECT MONTH("2015-09-10")<MONTH("2016-04-24"); 68 month(2015-09-10) < month(2016-04-24) 69 false 70 SELECT MONTH("2015-09-10")=MONTH("2016-04-24"); 71 month(2015-09-10) = month(2016-04-24) 72 false 73 SELECT MONTH("2015-09-10")<>MONTH("2016-04-24"); 74 month(2015-09-10) != month(2016-04-24) 75 true 76 SELECT MONTH("2015-09-10")!=MONTH("2016-04-24"); 77 month(2015-09-10) != month(2016-04-24) 78 true 79 SELECT MONTH(NULL) AS Month ; 80 Month 81 null 82 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)); 83 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' ) ; 84 invalid input: invalid default value for column 'product_id' 85 SELECT MONTH (Selling_Date) month, COUNT(Product_id) Product_Sold FROM Product GROUP BY MONTH (Selling_Date) ORDER BY MONTH (Selling_Date); 86 month Product_Sold 87 DROP TABLE Product; 88 drop table if exists t1; 89 create table t1(a INT, b date); 90 insert into t1 select month("2012-10-12"), "2012-10-12"; 91 insert into t1 select month("2004-04-24"), "2004-04-24"; 92 insert into t1 select month("2008-12-04"), "2008-12-04"; 93 select * from t1; 94 a b 95 10 2012-10-12 96 4 2004-04-24 97 12 2008-12-04 98 drop table t1; 99 drop table if exists t1; 100 create table t1(a INT, b date); 101 insert into t1 values(1, "2012-10-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23"); 102 select * from t1 where month(b)=10; 103 a b 104 1 2012-10-12 105 drop table t1; 106 create table t1(a INT, b date); 107 create table t2(a INT, b date); 108 insert into t1 values(1, "2012-10-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23"); 109 insert into t2 values(1, "2013-04-30"),(2, "1994-10-04"),(3, "2018-06-04"),(4, "2012-08-12"); 110 SELECT t1.a, t2.a FROM t1 JOIN t2 ON (month(t1.b) = month(t2.b)); 111 a a 112 2 1 113 1 2 114 drop table t1; 115 drop table t2; 116 drop table if exists t1; 117 create table t1(a INT, b date); 118 insert into t1 values(1, "2012-10-12"),(1, "2012-07-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23"); 119 select b from t1 group by b having month(b)=12; 120 b 121 2008-12-04 122 drop table t1; 123 drop table if exists t1; 124 create table t1(a INT, b date); 125 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"); 126 select distinct month(b) from t1; 127 month(b) 128 10 129 7 130 4 131 12 132 3 133 drop table t1; 134 CREATE TABLE t3(c1 DATE NOT NULL); 135 INSERT INTO t3 VALUES('2000-01-01'); 136 INSERT INTO t3 VALUES('1999-12-31'); 137 INSERT INTO t3 VALUES('2000-01-01'); 138 INSERT INTO t3 VALUES('2006-12-25'); 139 INSERT INTO t3 VALUES('2008-02-29'); 140 SELECT MONTH(c1) FROM t3; 141 MONTH(c1) 142 1 143 12 144 1 145 12 146 2 147 DROP TABLE t3; 148 CREATE TABLE t3(c1 DATETIME NOT NULL); 149 INSERT INTO t3 VALUES('2000-01-01'); 150 INSERT INTO t3 VALUES('1999-12-31'); 151 INSERT INTO t3 VALUES('2000-01-01'); 152 INSERT INTO t3 VALUES('2006-12-25'); 153 INSERT INTO t3 VALUES('2008-02-29'); 154 SELECT MONTH(c1) FROM t3; 155 MONTH(c1) 156 1 157 12 158 1 159 12 160 2 161 DROP TABLE t3; 162 CREATE TABLE t3(c1 TIMESTAMP NOT NULL); 163 INSERT INTO t3 VALUES('2000-01-01'); 164 INSERT INTO t3 VALUES('1999-12-31'); 165 INSERT INTO t3 VALUES('2000-01-01'); 166 INSERT INTO t3 VALUES('2006-12-25'); 167 INSERT INTO t3 VALUES('2008-02-29'); 168 SELECT MONTH(c1) FROM t3; 169 MONTH(c1) 170 1 171 12 172 1 173 12 174 2 175 DROP TABLE t3;