The DAYOFYEAR() function in MySQL returns the day of the year for a given date, represented as a number between 1 and 366. It is useful for determining which day of the year a specific date corresponds to.
Definition and Usage
- The
DAYOFYEAR()function returns an integer representing the day of the year for a given date. - The result is a number between 1 and 366:
- 1 corresponds to January 1st.
- 366 corresponds to December 31st on a leap year.
Syntax
DAYOFYEAR(date)
- date: Required. A valid date or datetime value for which the day of the year is to be calculated.
Examples
Example 1: Return the day of the year for a specific date
SELECT DAYOFYEAR("2017-06-15");
Output:
166
In this case, June 15th, 2017, is the 166th day of the year.
Example 2: Return the day of the year for the first day of the year
SELECT DAYOFYEAR("2017-01-01");
Output:
1
The first day of the year (January 1st) is always day 1.
Example 3: Return the day of the year for the current system date
SELECT DAYOFYEAR(CURDATE());
Output:
355
If today's date is December 21st, it would be the 355th day of the year.
Use Cases
- Yearly Reporting: To calculate how many days have passed in the year for reports or data analysis.
- Event Scheduling: To track events or deadlines in terms of the day of the year rather than specific dates.
- Date Comparison: To compare how far apart two dates are within the same year.
Related Functions
DAYOFWEEK(): Returns the weekday index (1 to 7), where Sunday is1and Saturday is7.DAYOFMONTH(): Returns the day of the month (1-31).WEEK(): Returns the week number of the year.DATE_FORMAT(): Allows formatting a date with custom patterns, including extracting the day of the year.