MySQL DAYOFYEAR() Function

MySQL DAYOFYEAR() function; In this tutorial, we are going to discuss briefly about the MySQL DAYOFYEAR function with the help of examples. And as well as, will take many examples of MySQL DAYOFYEAR function with other MySQL functions like CURDATE, Now etc.

When we fetch the records from the database, if we want to return the day of the year of any date. In that case, we can use the DAYOFYEAR function.

MySQL DAYOFYEAR() Function

In MySQL, the DAYOFYEAR() is used to return the day of the year from a date. It will returns always a numeric value between 1 and 366

The DAYOFYEAR () function is very simple to use with MySQL queries. Let’s see the syntax and examples below.

Syntax

Basic Syntax of DAYOFYEAR() function is:

DAYOFYEAR(date)

The date here is the date value that you want the day of the year from which it was returned.

Example-1

Now let’s take an example to explain.

SELECT DAYOFYEAR('2020-06-18') AS 'Result';

Output-1

+--------+
| Result |
+--------+
|    170 |
+--------+

Example-2

Let’s take another example to explain with different date.

SELECT DAYOFYEAR('2018-02-01') AS 'Result';

Output-2

+--------+
| Result |
+--------+
|    32  |
+--------+

Example-3 With Database Query

Next database example, when we get records from the database at that time we want to remove the day of the year from create_at(or any date column of your table)

 SELECT
 created_at AS create_date,
 DAYOFYEAR(created_at) AS day_of_year
 FROM users
 WHERE id= 112;

Output-3

+---------------------+--------------+
| create_date         | day_of_year |
+---------------------+--------------+
| 2010-08-23 10:33:39 |       235    |
+---------------------+--------------+

Example-4 Using Current Date/Time

Let’s take a new example, extract the day of the year from the current date and time (which is now returned using the () function).

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

Output-4

+---------------------+--------------------+
| NOW()               | DAYOFYEAR(NOW())   |
+---------------------+--------------------+
| 2019-07-12 18:30:44 |        193         |
+---------------------+--------------------+

Example-5 With MySQL CURDATE() Function

Let’s take the next example of using CURDATE () function. If you want to know more about CURDATE () function click here.

SELECT
CURDATE(),
DAYOFYEAR(CURDATE());

Output-5

+------------+-----------------------+
| CURDATE()  | DAYOFYEAR(CURDATE())  |
+------------+-----------------------+
| 2019-07-12 |             193       |
+------------+-----------------------+

Conclusion

Here, You have learned how to use mysql DAYOFYEAR() function with various examples.

Recommended MySQL Tutorials

If you have any questions or thoughts to share, use the comment form below to reach us.