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