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

Dayname Monthname

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)

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


Syntax of MySQL MONTHNAME()

MONTHNAME(date_value)

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;

And we get the output as follows –

Dayname Basic Example

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;

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 Datetime Value

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;

And we get the output as –

Dayname Null

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;

And we get the output as –

Dayname Wrongdate

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;

And we get the output as follows –

Monthname Basic Example

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;

We get the output as –

Monthname Datetime

MONTHNAME() With NULL

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

SELECT MONTHNAME(NULL) AS Month;

And we get the output as –

Monthname Null

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;

And we get the output as –

Monthname Wrongdate

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.

Dayname Monthname Employee Table
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;

And the output is –

Table Example1 Dayname

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;

And the output is –

Table Example2 Monthname

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;

And we get the output as –

Table Example3 Dayname Order By

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;

And the output is –

Table Example4 Monthname Order By

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