MySQL DAYNAME() Function

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

Introduction to MySQL DAYNAME() function

The DAYNAME function allows you to get the the name of a day for a specified date. The following illustrates the syntax of the DAYNAME function:

DAYNAME(date);Code language: SQL (Structured Query Language) (sql)

In this syntax:

date: This is a DATE or DATETIME value that you want to get the day’s name.

The DAYNAME() function returns a string that represents the day name for a date.

By default, the DAYNAME() function returns the name of the day in the locale which is set by the lc_time_names system variable.

To show the current value of the variable, you use the following statement:

SELECT @@lc_time_names;Code language: CSS (css)

Output:

+-----------------+
| @@lc_time_names |
+-----------------+
| en_US           |
+-----------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

The output shows that the current locale is en_US. It means that the DAYNAME() will return the string "Monday" if the date is Monday:

SELECT DAYNAME('2023-10-16');Code language: JavaScript (javascript)

Output:

+-----------------------+
| DAYNAME('2023-10-16') |
+-----------------------+
| Monday                |
+-----------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

If you want to get the day name in a specific locale, you need to change the value of the lc_time_names variable.

For example, the following assigns the value 'fr_FR' to the lc_time_names variable that sets the locale to French:

SET @@lc_time_names = 'fr_FR';Code language: CSS (css)

Here’s the day’s name in French:

SELECT DAYNAME('2023-10-16');Code language: JavaScript (javascript)

Output:

+-----------------------+
| DAYNAME('2023-10-16') |
+-----------------------+
| lundi                 |
+-----------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

The DAYNAME() function returns NULL if the date is NULL or invalid e.g., 2017-02-30.

MySQL DAYNAME() function examples

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

1) Simple DAYNAME() function example

The following example uses the DAYNAME() function to return the name of a day for January 1st, 2000:

SELECT DAYNAME('2000-01-01') dayname;Code language: JavaScript (javascript)

Output:

+----------+
| dayname  |
+----------+
| Saturday |
+----------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

2) Using the DAYNAME() function with table data

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

The following statement uses the DAYNAME() function to get the order count grouped by the day name in 2004.

SELECT 
  DAYNAME(orderdate) day, 
  COUNT(*) total_orders 
FROM 
  orders 
WHERE 
  YEAR(orderdate) = 2004 
GROUP BY 
  day
ORDER BY 
  total_orders DESC;Code language: SQL (Structured Query Language) (sql)

Output:

+-----------+--------------+
| day       | total_orders |
+-----------+--------------+
| Friday    |           35 |
| Wednesday |           29 |
| Thursday  |           26 |
| Monday    |           24 |
| Tuesday   |           24 |
| Saturday  |           11 |
| Sunday    |            2 |
+-----------+--------------+
7 rows in set (0.00 sec)Code language: JavaScript (javascript)

The number of orders placed on Friday is the highest and there were only two orders placed on Sunday.

Summary

Use the DAYNAME() function to get the name of the day for a specific date.

Was this tutorial helpful?