MySQL DAYOFYEAR() Function

 The DAYOFYEAR() function in MySQL is used to return the day of the year for a given date. The value ranges from 1 to 366, depending on whether the date falls in a leap year or not.

Syntax:

DAYOFYEAR(date)

• date: The date for which the day of the year is to be returned. It can be a DATE, DATETIME, or a string that can be converted into a date.

Returns:

• An integer representing the day of the year.

Example Queries:

1. Basic Usage

SELECT DAYOFYEAR('2024-01-01') AS DayOfYear; 
-- Output: 1 (January 1 is the first day of the year)

2. Leap Year Example

SELECT DAYOFYEAR('2024-12-31') AS DayOfYear;
-- Output: 366 (2024 is a leap year)

3. Non-Leap Year Example

SELECT DAYOFYEAR('2023-12-31') AS DayOfYear;
-- Output: 365

4. Current Date

SELECT DAYOFYEAR(CURDATE()) AS TodayDayOfYear;

Notes:

• If the input date is invalid or NULL, the function will return NULL.
• This function is useful for calculating the ordinal position of a date within a year, which can be applied in seasonal analysis or date comparisons.