When running queries in MySQL, you can use the DAYOFYEAR() function to return the day of the year from a date.

This function accepts one argument, and it returns a value between 1 and 366, depending on which day of the year the day part of the argument represents.

Syntax

The syntax goes like this:

DAYOFYEAR(date)

Where date is the date value that you want the day of the year returned from.

Example

Here’s an example to demonstrate.

SELECT 
    DAYOFYEAR('2019-01-01') AS 'Date 1',
    DAYOFYEAR('2019-12-31') AS 'Date 2';

Result:

+--------+--------+
| Date 1 | Date 2 |
+--------+--------+
|      1 |    365 |
+--------+--------+

In this example, in order to demonstrate the proper effect, I run this function twice on two different dates – one at the start of the year, and the other at the end of the year.

DAYOFYEAR() vs DAYOFMONTH()

Here’s an example to demonstrate the difference between the DAYOFYEAR() and DAYOFMONTH() functions.

SET @date = '2019-10-03';
SELECT 
    @date,
    DAYOFMONTH(@date) AS 'Day of Month',
    DAYOFYEAR(@date) AS 'Day of Year';

Result:

+------------+--------------+-------------+
| @date      | Day of Month | Day of Year |
+------------+--------------+-------------+
| 2019-10-03 |            3 |         276 |
+------------+--------------+-------------+

The DAYOFMONTH() function returns a value between 1 and 31 (or 0 for dates with a zero day part) that represents the day of the month. It resets back to 1 at the start of each month.

The DAYOFYEAR() function on the other hand, returns a value between 1 and 366. It resets back to 1 at the start of each year.

A Database Example

Here’s an example of using DAYOFYEAR() when querying a database:

USE sakila;
SELECT
  payment_date AS 'Date/Time',
  DAYOFYEAR(payment_date) AS 'Day of Year'
FROM payment
WHERE payment_id = 1;

Result:

+---------------------+-------------+
| Date/Time           | Day of Year |
+---------------------+-------------+
| 2005-05-25 11:30:37 |         145 |
+---------------------+-------------+

Current Date/Time

Here’s an example of extracting the day part from the current date and time (which is returned using the NOW() function).

SELECT 
    NOW(),
    DAYOFYEAR(NOW());

Result:

+---------------------+------------------+
| NOW()               | DAYOFYEAR(NOW()) |
+---------------------+------------------+
| 2018-06-26 08:23:04 |              177 |
+---------------------+------------------+

Another way to do this is to use the CURDATE() function, which returns only the date (but not the time).

SELECT 
    CURDATE(),
    DAYOFYEAR(CURDATE()); 

Result:

+------------+----------------------+
| CURDATE()  | DAYOFYEAR(CURDATE()) |
+------------+----------------------+
| 2018-06-26 |                  177 |
+------------+----------------------+