MySQL DAY(), MONTH() and YEAR() – Date Functions in MySQL

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

• The DAY() function is used to return the day of a month for a given date. As expected, the returned value is a numerical value from 1 to 31.
• The MONTH() function is used to return the month from a given date. As expected, the value returned is a numerical value from 1 to 12.
• Finally, the YEAR() function is used to return a year part from a given date value. The function returns a numerical value from 0 to 9999.

Syntax of MySQL DAY()

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

Where ‘date_value’ is the date from which you want to find the day number.

Syntax of MySQL MONTH()

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

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

Syntax of MySQL YEAR()

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

Where ‘date_value’ is the date from which you want to find the year number.

Examples of MySQL DAY()

Let us kick things off with a simple example. Using the SELECT Statement and an alias called Day, we will find the day number in the following date value – ‘2019-05-25’.

SELECT DAY('2019-05-25') AS Day;Code language: SQL (Structured Query Language) (sql)

And we get the output as follows –

MySQL DAY() With A 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 DAY('2014-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 DAY() With NULL

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

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

And the output is –

MySQL DAY() With Wrong Day Value

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

 SELECT DAY('2019-05-42') AS Day;Code language: SQL (Structured Query Language) (sql)

And the output is –

Examples of MySQL MONTH()

Let us now look at some simple examples of the MONTH() function. Let us find the month number in the following date value – ‘2012-12-31’.

SELECT MONTH('2012-12-31') AS Month;Code language: SQL (Structured Query Language) (sql)

And we get the output as follows –

MySQL MONTH() With A Datetime Value

Let us pass a datetime value as an argument. Consider the below query.

SELECT MONTH('2014-07-06 13:36:54') AS Month;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 –

MONTH() With NULL

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

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

And the output is –

MONTH() With Wrong Month Value

What if we specify a wrong month value (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 MONTH('2012-16-01') AS Month;Code language: SQL (Structured Query Language) (sql)

And the output is –

Examples of MySQL YEAR()

Let us now look at some simple examples of the YEAR() function. Let us find the year number in the following date value – ‘2016-04-01’.

SELECT YEAR(‘2016-04-01’) AS YEAR;Code language: SQL (Structured Query Language) (sql)

And we get the output as follows –

YEAR() With A Datetime Value

Let us pass a datetime value as an argument. Consider the below query.

SELECT YEAR('2021-07-06 13:36:54') AS YEAR;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 –

YEAR() With NULL

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

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

And the output is –

YEAR() With Wrong Month Value

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

SELECT YEAR('20161-04-01') AS YEAR;Code language: SQL (Structured Query Language) (sql)

And the output is –

DAY(), MONTH() and YEAR() With Tables

Consider the below Employee table.

With The ORDER BY Clause

Let us use the ORDER BY clause and display the ascending order of the joining date of every employee by the year value. The query is,

SELECT * FROM Employee ORDER BY YEAR(Date_Joined);Code language: SQL (Structured Query Language) (sql)

And we get the output as follows –

With The WHERE Clause

Let us find out the list of employees who joined the company at the beginning of any year i.e. in January. We will use the WHERE clause. The query is –

SELECT * FROM Employee WHERE MONTH(Date_Joined)=1;Code language: SQL (Structured Query Language) (sql)

And we get the output as –

With Aggregate Functions

Let us find the number of employees who joined the company on the 5th day of any month in any given year. We will use the COUNT() function for this. The query is –

SELECT COUNT(*) AS NumberOfEmployees FROM Employee WHERE DAY(Date_Joined)=5;Code language: SQL (Structured Query Language) (sql)

And we get the output as,

Conclusion

The DAY(), MONTH() and YEAR() functions are widely used while retrieving the day, month, and year values respectively from a date value. I would encourage you to practice different examples with different MySQL clauses.

References

• MySQL Official Documentation on DAY(), MONTH() and YEAR().