Tutorial Gateway

MySQL DAYOFYEAR Function

by

MySQL DAYOFYEAR function is a Date Function, which is used to return the day number of year from the given date. This function returns value from 1 to 366.

In this article, we will show you, How to use this DAYOFYEAR function to get the day number of the year or Day of Year in MySQL from an expression with example.

MySQL DAYOFYEAR Syntax

The basic syntax of the DAYOFYEAR() Function in MySQL is as shown below:

DAYOFYEAR(date or expression);

MySQL DAYOFYEAR function Example

These examples helps you understand the use of dayofyear function. Here, we are returning the day of year number from the date expression, and the Date & time expression.

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

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

SELECT DAYOFYEAR('2018-05-26 01:09:22');

OUTPUT

MySQL DAYOFYEAR Function 1

Let us see another example of MySQL DAYOFYEAR function. Here, we are returning the day of year number from the current date and time returned by the Now(), and CURDATE() function.

SELECT DAYOFYEAR(NOW()), DAYOFYEAR(CURRENT_DATE());

OUTPUT

MySQL DAYOFYEAR Function 2

MySQL DAYOFYEAR Function Example 2

The following queries show you, what happens when we try to return the day of year value from string date, 0 date format, or invalid dates.

SELECT DAYOFYEAR(NOW() + 2);

SELECT DAYOFYEAR('2018-22-14');

SELECT DAYOFYEAR('2018-00-00');

OUTPUT

MySQL DAYOFYEAR Function 3

From the above screenshot, DAYOFYEAR(‘2018-22-14’); statement returned NULL. Because, it’s an invalid date.

Within the Last statement, DAYOFYEAR(‘2018-00-00’) means, we are trying to extract day of year from a zero date part. That’s why, it returns NULL.

Thank You for Visiting Our Blog