DAYOFYEAR()

函数说明

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

函数语法

  1. > DAYOFYEAR(expr)

参数释义

参数说明
expr必要参数。需要提取天数的 date 格式的输入值

示例

  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');
  9. mysql> select distinct dayofyear(a) as dya from t1;
  10. +------+
  11. | dya |
  12. +------+
  13. | 1 |
  14. | 2 |
  15. | 365 |
  16. | 181 |
  17. +------+
  18. 4 rows in set (0.00 sec)
  19. mysql> select * from t1 where dayofyear(a)>120;
  20. +------------+---------------------+---------------------+
  21. | a | b | c |
  22. +------------+---------------------+---------------------+
  23. | 2021-12-31 | 2021-12-30 23:59:59 | 2021-12-30 23:59:59 |
  24. | 2022-06-30 | 2021-12-30 23:59:59 | 2021-12-30 23:59:59 |
  25. +------------+---------------------+---------------------+
  26. 2 rows in set (0.01 sec)
  27. mysql> select * from t1 where dayofyear(a) between 1 and 184;
  28. +------------+---------------------+---------------------+
  29. | a | b | c |
  30. +------------+---------------------+---------------------+
  31. | 2022-01-01 | 2022-01-01 01:01:01 | 2022-01-01 01:01:01 |
  32. | 2022-01-01 | 2022-01-01 01:01:01 | 2022-01-01 01:01:01 |
  33. | 2022-01-01 | 2022-01-01 01:01:01 | 2022-13-13 01:01:01 |
  34. | 2022-01-02 | 2022-01-02 23:01:01 | 2022-01-01 23:01:01 |
  35. | 2022-06-30 | 2021-12-30 23:59:59 | 2021-12-30 23:59:59 |
  36. +------------+---------------------+---------------------+
  37. 4 rows in set (0.00 sec)

限制

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