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 Day of Year in MySQL 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 day of year method. Here, we are returning the day of year number from the date expression, and the DateTime expression.

SELECT DAYOFYEAR('2017-12-31');

SELECT DAYOFYEAR('2017-01-01');

SELECT DAYOFYEAR('2018-05-26 01:09:22');
MySQL DAYOFYEAR Function 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());
MySQL DAYOFYEAR Function 2

DAY OF YEAR Example 2

The following MySQL DAYOFYEAR 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’); 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.