DAYOFYEAR()

Function Description

Returns the number of days in the year corresponding to the date, and the return value is between 1-366.

Function Syntax

> DAYOFYEAR(expr)

Parameter definition

Parameters	Description
expr	Required parameters. Input value in date format that requires extracting the number of days

Example

drop table if exists t1;
create table t1(a date, b datetime,c varchar(30));
insert into t1 values('2022-01-01','2022-01-01 01:01:01','2022-01-01 01:01:01');
insert into t1 values('2022-01-01','2022-01-01 01:01:01','2022-01-01 01:01:01');
insert into t1 values('20220101','2022-01-01 01:01:01','2022-13-13 01:01:01');
insert into t1 values('2022-01-02','2022-01-02 23:01:01','2022-01-01 23:01:01');
insert into t1 values('2021-12-31','2021-12-30 23:59:59','2021-12-30 23:59:59');
insert into t1 values('2022-06-30','2021-12-30 23:59:59','2021-12-30 23:59:59');

mysql> select distinct dayofyear(a) as dya from t1;
+------+
| dya |
+------+
| 1 |
| 2 |
| 365 |
| 181 |
+------+
4 rows in set (0.00 sec)

mysql> select * from t1 where dayofyear(a)>120;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| a | b | c |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 2021-12-31 | 2021-12-30 23:59:59 | 2021-12-30 23:59:59 |
| 2022-06-30 | 2021-12-30 23:59:59 | 2021-12-30 23:59:59 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2 rows in set (0.01 sec)

mysql> select * from t1 where dayofyear(a) between 1 and 184;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| a | b | c |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 2022-01-01 | 2022-01-01 01:01:01 | 2022-01-01 01:01:01 |
| 2022-01-01 | 2022-01-01 01:01:01 | 2022-01-01 01:01:01 |
| 2022-01-01 | 2022-01-01 01:01:01 | 2022-13-13 01:01:01 |
| 2022-01-02 | 2022-01-02 23:01:01 | 2022-01-01 23:01:01 |
| 2022-06-30 | 2021-12-30 23:59:59 | 2021-12-30 23:59:59 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4 rows in set (0.00 sec)

limit

• Currently DAYOFYEAR() only supports date type.
• Currently, the date format only supports the data formats of yyyy-mm-dd and yyyymmdd.