MySQL DAYOFYEAR is one of the Date Functions, which returns the day number of the year from the given date. This function returns a value from 1 to 366.
Let us see how to use this MySQL DAYOFYEAR function to get the day number of the year or the Day of the Year from an expression with an example. The basic syntax of the DAYOFYEAR() method is as shown below:
DAYOFYEAR(date or expression);
MySQL DAYOFYEAR function Example
These examples help you understand the use of the day of year method. Here, we are returning the day of year number from the date and date time expressions.
SELECT DAYOFYEAR('2017-12-31');
SELECT DAYOFYEAR('2017-01-01');
SELECT DAYOFYEAR('2018-05-26 01:09:22');
Let us see another example of the MySQL DAYOFYEAR function. Here, we are returning the day of year number from the current DateTime returned by the Now() and CURDATE() methods.
SELECT DAYOFYEAR(NOW()), DAYOFYEAR(CURRENT_DATE());
MySQL DAYOFYEAR Example 2
The following MySQL DAY OF YEAR example queries shows you what happens when we try to return the day of year value from string date, 0 format, or invalid dates.
SELECT DAYOFYEAR(NOW() + 2);
SELECT DAYOFYEAR('2018-22-14');
SELECT DAYOFYEAR('2018-00-00');
From the above Date method screenshot (‘2018-22-14’); the statement returned NULL. Because it’s an invalid date. Within the Last statement, (‘2018-00-00’) means we are trying to extract the day of the year from a zero datepart. That’s why it returns NULL.
