MySQL - DAYOFYEAR() Function



The MySQL DAYOFYEAR() function is used to retrieve the day of the year for the given date. This function returns the numerical value representing the day of the year which will be a number from 1 to 366.

The day of year here refers to numerical values assigned to the date starting from 1st January as 1 to 365 (or 366 if leap year). Hence, the result of this function always lies between 1 and 366.

The function will return NULL if none, an empty string or an invalid date is passed to the it.

Syntax

Following is the syntax of MySQL DAYOFYEAR() function −

DAYOFYEAR(date);

Parameters

This method accepts the date value from which you need to get the day of the year as a parameter.

Return value

This function returns the day of the year for the provided date.

Example

In the following query, we are using the MySQL DAYOFYEAR() function to extract the day of the year for the given date −

SELECT DAYOFYEAR('2019-05-25') As Result;

Output

The output for the query above is produced as given below −

Result
145

Example

If the day part in the given date is 0 this function returns NULL —

SELECT DAYOFYEAR('2017-00-00') As Result;

Output

This will produce the following result −

Result
0

Example

If you pass an empty string or a non-string value as an argument this function returns NULL.

SELECT DAYOFYEAR('')  As Result;

This will produce the following result −

Result
NULL

Here, we are passing a non-string value as an argument this function −

SELECT DAYOFYEAR(1990-11-11) As Result;

This will produce the following result −

Result
NULL

Example

We can also pass the date-time expression as an argument to this function −

SELECT DAYOFYEAR('2015-09-05 09:40:45.2300') As Result;

Output

This will produce the following result −

Result
248

Example

In the following example, we are retrieving the day of the year value from the current date −

SELECT DAYOFYEAR(CURDATE()) As Result;

Output

This will produce the following result −

Result
315

Example

We can also pass the column name as an argument to this function. Let us create a table named ORDERS using CREATE statement as shown below −

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2)
);

Now, insert the following records into the ORDERS table using the INSERT statement −

INSERT INTO ORDERS VALUES 
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);

Execute the below query to fetch all the inserted records in the above-created table −

Select * From ORDERS;

Following is the ORDERS table −

OID DATE CUSTOMER_ID AMOUNT
102 2009-10-08 00:00:00 3 3000.00
100 2009-10-08 00:00:00 3 1500.00
101 2009-11-20 00:00:00 2 1560.00
103 2008-05-20 00:00:00 4 2060.00

Now, we are using the MySQL DAYOFYEAR() function to retrieve the day of the year for all the datetime values in the DATE column.

SELECT OID, DATE, DAYOFYEAR(DATE) From ORDERS;

Output

The output is displayed as follows −

OID DATE DAYOFYEAR(DATE)
102 2009-10-08 00:00:00 281
100 2009-10-08 00:00:00 281
101 2009-11-20 00:00:00 324
103 2008-05-20 00:00:00 141
Advertisements