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

Date Month Year

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)

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


Syntax of MySQL MONTH()

MONTH(date_value)

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


Syntax of MySQL YEAR()

YEAR(date_value)

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;

And we get the output as follows –

Day Basic Example

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;

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 –

Day Datetime Example

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;

And the output is –

Day Null Example

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;

And the output is –

Day Wrong Value

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;

And we get the output as follows –

Month Basic Example

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;

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

MONTH() With NULL

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

SELECT MONTH(NULL) AS Month;

And the output is –

Month Null Example

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;

And the output is –

Month Wrong Value

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;

And we get the output as follows –

Year Basic Example

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;

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

YEAR() With NULL

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

SELECT YEAR(NULL) AS YEAR;

And the output is –

Year Null Example

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;

And the output is –

Year Wrong Value

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

Consider the below Employee table.

Employee Table Day Month Year
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);

And we get the output as follows –

Table Example 1

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;

And we get the output as –

Table Example 2

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;

And we get the output as,

Table Example 3

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