MySQL DAYOFYEAR

Summary: in this tutorial, you will learn how to use the MySQL DAYOFYEAR() function to get the day of the year for a specific date.

Introduction to MySQL DAYOFYEAR() function

The DAYOFYEAR() function allows you to get the day of the year for a date.

Here’s the syntax of the DAYOFYEAR() function:

DAYOFYEAR(date)Code language: SQL (Structured Query Language) (sql)

In this syntax:

date: This is the date for which you want to determine the day of the year.

The function returns the day of the year (1 to 366) from the given date. It returns NULL if the date is NULL.

MySQL DAYOFYEAR() function examples

Let’s take some examples of using the DAYOFYEAR() function.

1) Basic the DAYOFYEAR() function example

The following example uses the DAYOFYEAR() function to get the day of the year for the date '2023-10-20':

SELECT DAYOFYEAR('2023-10-20')Code language: SQL (Structured Query Language) (sql)

Output:

+-------------------------+
| DAYOFYEAR('2023-10-20') |
+-------------------------+
|                     293 |
+-------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

2) Using the DAYOFYEAR() function with table data

We’ll use the orders table from the sample database:

The following example uses the DAYOFYEAR() function to get the day of the year for each order:

SELECT
  orderNumber, 
  orderDate, 
  DAYOFYEAR(orderDate) 
FROM
  orders;Code language: SQL (Structured Query Language) (sql)

Output:

+-------------+------------+----------------------+
| orderNumber | orderDate  | dayofyear(orderDate) |
+-------------+------------+----------------------+
|       10100 | 2003-01-06 |                    6 |
|       10101 | 2003-01-09 |                    9 |
|       10102 | 2003-01-10 |                   10 |
|       10103 | 2003-01-29 |                   29 |
|       10104 | 2003-01-31 |                   31 |
...Code language: SQL (Structured Query Language) (sql)

In this example, we used the DAYOFYEAR() function to get the day of the year from the values in the orderDate column.

Summary

Use the DAYOFYEAR() function to obtain the day of the year.

Was this tutorial helpful?