How to use the MySQL `DAYOFYEAR()` function

The DAYOFYEAR() function in MySQL returns the day of the year for a given date. This can calculate the numeric day from 1 to 365 (or 366 in leap years).

Syntax

The syntax for DAYOFYEAR() is:

DAYOFYEAR(date)

Where date is a DATE or DATETIME value.

Examples

Some examples of using DAYOFYEAR() in MySQL:

SELECT DAYOFYEAR('2023-11-14');

-- Returns 318

Get the day number of the year for the date ‘2023-11-14’.

SELECT name, DAYOFYEAR(birthday)
FROM users;

Retrieve the name and numeric day of the year for each user’s birthday.

SELECT name
FROM users
WHERE DAYOFYEAR(birthday) = 100;

Get names of users with birthdays on the 100th day of the year.

SELECT DAYOFYEAR(NOW()) - DAYOFYEAR('2023-01-01');

-- Returns 314

Calculate number of days elapsed since the beginning of the year.

SELECT DATE_ADD('2023-01-01', INTERVAL `DAYOFYEAR`(NOW()) DAY);

-- Returns 2023-11-14 00:00:00

Use DAYOFYEAR() to increment the start of the year by the current day number.

Other Date Functions

Some other useful MySQL date functions:

• DAYOFWEEK() - Day of week from date
• WEEKDAY() - Weekday name from date
• MONTH() - Numeric month from date
• QUARTER() - Quarter of year from date
• WEEK() - Week number in year for date

DAYOFYEAR() can be combined with these to manipulate dates.