DAYOFYEAR() Function Examples – MySQL

In this tutorial, we are going to discuss briefly about the MySQL DAYOFYEAR function with its syntax and more useful examples. We will take different examples of DAYOFYEAR function with MySQL like CURDATE, Now etc.

When we fetch the records from the database, if we want to return the day of 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 for 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 day of 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-4 With MySQL CURDATE() Function

Let’s take a 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.

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