DAYOFYEAR()

函数说明

返回日期所对应在一年中的天数，返回值在 1-366 之间。

函数语法

1. > DAYOFYEAR(expr)

参数释义

示例

1. drop table if exists t1;
2. create table t1(a date, b datetime,c varchar(30));
3. insert into t1 values('2022-01-01','2022-01-01 01:01:01','2022-01-01 01:01:01');
4. insert into t1 values('2022-01-01','2022-01-01 01:01:01','2022-01-01 01:01:01');
5. insert into t1 values('20220101','2022-01-01 01:01:01','2022-13-13 01:01:01');
6. insert into t1 values('2022-01-02','2022-01-02 23:01:01','2022-01-01 23:01:01');
7. insert into t1 values('2021-12-31','2021-12-30 23:59:59','2021-12-30 23:59:59');
8. insert into t1 values('2022-06-30','2021-12-30 23:59:59','2021-12-30 23:59:59');

10. mysql> select distinct dayofyear(a) as dya from t1;
11. +------+
12. | dya  |
13. +------+
14. |    1 |
15. |    2 |
16. |  365 |
17. |  181 |
18. +------+
19. 4 rows in set (0.00 sec)

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

30. mysql> select * from t1 where dayofyear(a) between 1 and 184;
31. +------------+---------------------+---------------------+
32. | a          | b                   | c                   |
33. +------------+---------------------+---------------------+
34. | 2022-01-01 | 2022-01-01 01:01:01 | 2022-01-01 01:01:01 |
35. | 2022-01-01 | 2022-01-01 01:01:01 | 2022-01-01 01:01:01 |
36. | 2022-01-01 | 2022-01-01 01:01:01 | 2022-13-13 01:01:01 |
37. | 2022-01-02 | 2022-01-02 23:01:01 | 2022-01-01 23:01:01 |
38. | 2022-06-30 | 2021-12-30 23:59:59 | 2021-12-30 23:59:59 |
39. +------------+---------------------+---------------------+
40. 4 rows in set (0.00 sec)

限制

• 目前 DAYOFYEAR() 只支持 date 类型。
• 目前 date 格式只支持 yyyy-mm-dd 和 yyyymmdd 的数据格式。