DAYOFYEAR()

Description

Returns the day of the year for date, in the range 1 to 366.

Syntax

  1. > DAYOFYEAR(expr)

Arguments

ArgumentsDescription
exprRequired. The date to get the day from. Must be date type.

Examples

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

Constraints

  • DAYOFYEAR() only supports date type for now.
  • The date type supports only yyyy-mm-dd and yyyymmdd for now.
  • Currently, MatrixOne doesn’t support select function() without from tables.