MySQL DAYOFYEAR Function 2

MySQL DAYOFYEAR Function

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 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');
DAY OF YEAR Example 1

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());
Example 2

DAY OF YEAR Example 2

The following MySQL DAY OF YEAR example queries show 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');
MySQL DAYOFYEAR Function 3

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.