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?