MySQL DAYNAME() and MONTHNAME() [With Easy Examples]

In this tutorial, we will study the MySQL DAYNAME() and MONTHNAME() functions. The MySQL DAYNAME() and MONTHNAME() functions are a part of the date functions in MySQL.

Suppose you have a date value and you want to know what day of the week it occurred. For instance, you have the date – 2021-01-12 and you want to know the weekday name of that date.

You pass that date to the DAYNAME() function, it will return ‘Tuesday’ to you. The DAYNAME() function is used to return the name of the weekday from a given specified date.

Now suppose you want to find the month name on the following date – 2021-09-11. If you pass this date to the MONTHNAME() function, it will return ‘September’ which is the name of the month on that date. The MONTHNAME() function is used to return the name of the month from a given date.

Syntax of MySQL DAYNAME()

DAYNAME(date_value)Code language: SQL (Structured Query Language) (sql)

Where ‘date_value’ is the date from which you want to find the weekday name.

Syntax of MySQL MONTHNAME()

MONTHNAME(date_value)Code language: SQL (Structured Query Language) (sql)

Where ‘date_value’ is the date from which you want to find the month name.

Examples of MySQL DAYNAME()

Let us start off by seeing a basic example of the DAYNAME() function. Let us find the name of the weekday on the following dates – ‘2020-06-09’ and ‘2021-02-14’.

SELECT DAYNAME('2020-06-09') AS Day; 
SELECT DAYNAME('2021-02-14') AS Day;Code language: SQL (Structured Query Language) (sql)

And we get the output as follows –

MySQL DAYNAME() With Datetime Value

Let us look at another basic example. This time let us pass a datetime value as an argument. Consider the below query.

SELECT DAYNAME('2021-11-06 13:36:54') AS Day;Code language: SQL (Structured Query Language) (sql)

The first part of the value is the date value and the second part of the value (i.e. ‘13:36:54’) is the time value in a 24-hour format. We get the output as –

MySQL DAYNAME() With NULL

MySQL DAYNAME() returns NULL if we pass a NULL value to it. Consider the below query.

SELECT DAYNAME(NULL) AS Day;Code language: SQL (Structured Query Language) (sql)

And we get the output as –

DAYNAME() With A Wrong Date Value

What if we specify a wrong date value (day value greater than 31 or month value greater than 12) in our ‘date_value’ parameter? Let us see what output, if at all, do we get. Consider the below query.

SELECT DAYNAME('2021-17-04') AS Day; 
SELECT DAYNAME('2021-07-64') AS Day;Code language: SQL (Structured Query Language) (sql)

And we get the output as –

As you can see, the MySQL DAYNAME() function returns NULL if a wrong date value is specified to it.

Examples of MySQL MONTHNAME()

Let us start off by seeing a basic example of the MONTHNAME() function. Let us find the name of the month in the following dates – ‘2020-06-09’ and ‘2021-02-14’.

SELECT MONTHNAME('2020-06-09') AS Month; 
SELECT MONTHNAME('2021-02-14') AS Month;Code language: SQL (Structured Query Language) (sql)

And we get the output as follows –

MySQL MONTHNAME() With Datetime Value

Let us look at another basic example. This time let us pass a datetime value as an argument. Consider the below query.

SELECT MONTHNAME('2011-11-08 13:36:54') AS Month;Code language: SQL (Structured Query Language) (sql)

We get the output as –

MONTHNAME() With NULL

MySQL MONTHNAME() returns NULL if we pass a NULL value to it. Consider the below query.

SELECT MONTHNAME(NULL) AS Month;Code language: SQL (Structured Query Language) (sql)

And we get the output as –

MONTHNAME() With A Wrong Date Value

What if we specify a wrong date value (day value greater than 31 or month value greater than 12) in our ‘date_value’ parameter? Let us see what output, if at all, do we get. Consider the below query.

SELECT MONTHNAME('2020-06-56') AS Month;Code language: SQL (Structured Query Language) (sql)

And we get the output as –

As you can see, the MONTHNAME() function returns NULL if a wrong date value is specified to it.

Using DAYNAME() and MONTHNAME() With Tables

Consider the below Employee table.

Basic Example

Let us start by finding out the name of the weekday on which every employee joined. Let us write a query using the SELECT statement and aliases that displays the eid, name, joining date of the employee and the name of the weekday on which the employee joined under the alias ‘Day’.

SELECT eid, Name, Date_Joined, DAYNAME(Date_Joined) AS Day FROM Employee;Code language: SQL (Structured Query Language) (sql)

And the output is –

Let us now find out the name of the month in which every employee joined. Let us write a similar query as above but this time we will display the month name in which the employee joined under the alias ‘Month’.

SELECT eid, Name, Date_Joined, MONTHNAME(Date_Joined) AS Month FROM Employee;Code language: SQL (Structured Query Language) (sql)

And the output is –

DAYNAME() and MONTHNAME() With COUNT() and the GROUP BY clause

Suppose you want to find the number of employees that joined on a Monday, Tuesday and so on. For this, we will make use of the COUNT() function and the GROUP BY clause. Our query is –

SELECT DAYNAME(Date_Joined) AS Day, Count(*) AS NumberOfEmployees FROM Employee GROUP BY Day;Code language: SQL (Structured Query Language) (sql)

And we get the output as –

Now what if we want to find the number of employees that joined the company by month name. Similar to the above, we will have the following query –

SELECT MONTHNAME(Date_Joined) AS Month, Count(*) AS NumberOfEmployees FROM Employee GROUP BY Month;Code language: SQL (Structured Query Language) (sql)

And the output is –

Conclusion

The DAYNAME() and MONTHNAME() functions are widely used while retrieving the name of the weekday and month respectively from a date value. I would encourage you to practice different examples with different MySQL clauses.

References

• MySQL Official Documentation on DAYNAME().
• MySQL Official Documentation on MONTHNAME().