github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/function/func_datetime_dayofyear.result (about) 1 SELECT dayofyear(date('2007-02-03 03:42:24')); 2 dayofyear(date('2007-02-03 03:42:24')) 3 34 4 SELECT dayofyear(NULL); 5 dayofyear(NULL) 6 null 7 create table t1(a tinyint, b SMALLINT, c bigint, d INT, e BIGINT, f FLOAT, g DOUBLE, h decimal(38,19), i DATE, k datetime, l TIMESTAMP, m char(255), n varchar(255)); 8 insert into t1 values(1, 1, 2, 43, 5, 35.5, 31.133, 14.314, "2012-03-10", "2012-03-12 10:03:12", "2022-03-12 13:03:12", "ab23c", "d5cf"); 9 insert into t1 values(71, 1, 2, 34, 5, 5.5, 341.13, 15.314, "2012-03-22", "2013-03-12 10:03:12", "2032-03-12 13:04:12", "abr23c", "3dcf"); 10 insert into t1 values(1, 1, 21, 4, 54, 53.5, 431.13, 14.394, "2011-03-12", "2015-03-12 10:03:12", "2002-03-12 13:03:12", "afbc", "dct5f"); 11 insert into t1 values(1, 71, 2, 34, 5, 5.5, 31.313, 124.314, "2012-01-12", "2019-03-12 10:03:12", "2013-03-12 13:03:12", "3abd1c", "dcvf"); 12 insert into t1 values(1, 1, 2, 34, 5, 5.5, 31.133, 14.314, "2023-11-08", "2023-11-08 15:03", "2023-11-08 15:03", "abcads3c", "dcdff"); 13 select dayofyear(a),dayofyear(b),dayofyear(c),dayofyear(d),dayofyear(e),dayofyear(f) from t1; 14 invalid argument function dayofyear, bad value [TINYINT] 15 select dayofyear(g),dayofyear(h),dayofyear(i),dayofyear(k),dayofyear(l),dayofyear(m),dayofyear(n) from t1; 16 invalid argument function dayofyear, bad value [DOUBLE] 17 drop table t1; 18 SELECT dayofyear("2015-09-03") as dayofyear; 19 dayofyear 20 246 21 SELECT dayofyear(20150904) as dayofyear; 22 invalid argument function dayofyear, bad value [BIGINT] 23 SELECT dayofyear(1340124) as dayofyear; 24 invalid argument function dayofyear, bad value [BIGINT] 25 SELECT dayofyear(0.45133) as dayofyear; 26 invalid argument function dayofyear, bad value [DECIMAL64] 27 SELECT dayofyear(10) as dayofyear; 28 invalid argument function dayofyear, bad value [BIGINT] 29 SELECT dayofyear(1=1) as dayofyear; 30 invalid argument function dayofyear, bad value [BOOL] 31 SELECT dayofyear('2015-09-30') as dayofyear; 32 dayofyear 33 273 34 SELECT dayofyear('2015-0') as dayofyear; 35 invalid argument parsedate, bad value 2015-0 36 SELECT dayofyear('2015-09.30 12') as dayofyear; 37 invalid argument parsedate, bad value 2015-09.30 12 38 SELECT dayofyear('2015-09-26 08:09:22') AS dayofyear; 39 dayofyear 40 269 41 select dayofyear("2020-08"); 42 invalid argument parsedate, bad value 2020-08 43 select dayofyear("0000-00-00"); 44 invalid argument parsedate, bad value 0000-00-00 45 select dayofyear("-0001-03-03"); 46 invalid argument parsedate, bad value -0001-03-03 47 select dayofyear("10000-03-02"); 48 invalid argument parsedate, bad value 10000-03-02 49 select dayofyear("2021-13-09"); 50 invalid argument parsedate, bad value 2021-13-09 51 select dayofyear("2020-10-32"); 52 invalid argument parsedate, bad value 2020-10-32 53 SELECT dayofyear("2015-09-10")- dayofyear("2016-04-24"); 54 dayofyear("2015-09-10")- dayofyear("2016-04-24") 55 138 56 SELECT dayofyear("2015-09-10")+ dayofyear("2016-04-24"); 57 dayofyear("2015-09-10")+ dayofyear("2016-04-24") 58 368 59 SELECT dayofyear("2015-09-10")*dayofyear("2016-04-24"); 60 dayofyear("2015-09-10")*dayofyear("2016-04-24") 61 29095 62 SELECT dayofyear("2015-09-10")/dayofyear("2016-04-24"); 63 dayofyear("2015-09-10")/dayofyear("2016-04-24") 64 2.2000 65 SELECT dayofyear("2015-09-10")>dayofyear("2016-04-24"); 66 dayofyear("2015-09-10")>dayofyear("2016-04-24") 67 true 68 SELECT dayofyear("2015-09-10")<dayofyear("2016-04-24"); 69 dayofyear("2015-09-10")<dayofyear("2016-04-24") 70 false 71 SELECT dayofyear("2015-09-10")=dayofyear("2016-04-24"); 72 dayofyear("2015-09-10")=dayofyear("2016-04-24") 73 false 74 SELECT dayofyear("2015-09-10")<>dayofyear("2016-04-24"); 75 dayofyear("2015-09-10")<>dayofyear("2016-04-24") 76 true 77 SELECT dayofyear("2015-09-10")!=dayofyear("2016-04-24"); 78 dayofyear("2015-09-10")!=dayofyear("2016-04-24") 79 true 80 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)); 81 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' ) ; 82 invalid input: invalid default value for column 'product_id' 83 SELECT dayofyear (Selling_Date) dayofyear, COUNT(Product_id) Product_Sold FROM Product GROUP BY dayofyear (Selling_Date) ORDER BY dayofyear (Selling_Date); 84 dayofyear Product_Sold 85 DROP TABLE Product; 86 drop table if exists t1; 87 create table t1(a INT, b date); 88 insert into t1 select dayofyear("2012-10-12"), "2012-10-12"; 89 insert into t1 select dayofyear("2004-04-24"), "2004-04-24"; 90 insert into t1 select dayofyear("2008-12-04"), "2008-12-04"; 91 select * from t1; 92 a b 93 286 2012-10-12 94 115 2004-04-24 95 339 2008-12-04 96 drop table t1; 97 drop table if exists t1; 98 create table t1(a INT, b date); 99 insert into t1 values(1, "2012-10-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23"); 100 select * from t1 where dayofyear(b)<>0; 101 a b 102 1 2012-10-12 103 2 2004-04-24 104 3 2008-12-04 105 4 2012-03-23 106 drop table t1; 107 drop table if exists t1; 108 drop table if exists t2; 109 create table t1(a INT, b date); 110 create table t2(a INT, b date); 111 insert into t1 values(1, "2012-10-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23"); 112 insert into t2 values(1, "2013-04-30"),(2, "1994-10-04"),(3, "2018-06-04"),(4, "2012-08-12"); 113 SELECT t1.a, t2.a FROM t1 JOIN t2 ON (dayofyear(t1.b) <> dayofyear(t2.b)); 114 a a 115 4 1 116 3 1 117 2 1 118 1 1 119 4 2 120 3 2 121 2 2 122 1 2 123 4 3 124 3 3 125 2 3 126 1 3 127 4 4 128 3 4 129 2 4 130 1 4 131 drop table t1; 132 drop table t2; 133 drop table if exists t1; 134 create table t1(a INT, b date); 135 insert into t1 values(1, "2012-10-12"),(1, "2012-07-12"),(2, "2004-04-24"),(3, "2008-12-04"),(4, "2012-03-23"); 136 select b from t1 group by b having dayofyear(b)>0; 137 b 138 2012-10-12 139 2012-07-12 140 2004-04-24 141 2008-12-04 142 2012-03-23 143 drop table t1; 144 drop table if exists t1; 145 create table t1(a INT, b date); 146 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"); 147 select distinct dayofyear(b) from t1; 148 dayofyear(b) 149 286 150 194 151 115 152 339 153 83 154 drop table t1; 155 CREATE TABLE t3(c1 DATE NOT NULL); 156 INSERT INTO t3 VALUES('2000-01-01'); 157 INSERT INTO t3 VALUES('1999-12-31'); 158 INSERT INTO t3 VALUES('2000-01-01'); 159 INSERT INTO t3 VALUES('2006-12-25'); 160 INSERT INTO t3 VALUES('2008-02-29'); 161 SELECT DAYOFYEAR(c1) FROM t3; 162 DAYOFYEAR(c1) 163 1 164 365 165 1 166 359 167 60 168 DROP TABLE t3; 169 CREATE TABLE t3(c1 DATETIME NOT NULL); 170 INSERT INTO t3 VALUES('2000-01-01'); 171 INSERT INTO t3 VALUES('1999-12-31'); 172 INSERT INTO t3 VALUES('2000-01-01'); 173 INSERT INTO t3 VALUES('2006-12-25'); 174 INSERT INTO t3 VALUES('2008-02-29'); 175 SELECT DAYOFYEAR(c1) FROM t3; 176 DAYOFYEAR(c1) 177 1 178 365 179 1 180 359 181 60 182 DROP TABLE t3; 183 CREATE TABLE t3(c1 TIMESTAMP NOT NULL); 184 INSERT INTO t3 VALUES('2000-01-01'); 185 INSERT INTO t3 VALUES('1999-12-31'); 186 INSERT INTO t3 VALUES('2000-01-01'); 187 INSERT INTO t3 VALUES('2006-12-25'); 188 INSERT INTO t3 VALUES('2008-02-29'); 189 SELECT DAYOFYEAR(c1) FROM t3; 190 DAYOFYEAR(c1) 191 1 192 365 193 1 194 359 195 60 196 DROP TABLE t3;