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.