How to extract month from date in MySQL

Hi Folks, In this article MySQL tutorial, I am about to teach you how to extract month from date in MySQL with the help of the examples. In real life project, you can have a requirement to apply some condition on the basis of the month on a date or DateTime column, In that case, you can use easily apply a function on the date or DateTime column to extract month from the date or DateTime column.

MySQL offers a date or DateTime function called MONTH() to extract only a month from any date or DateTime column. let’s see with the help of the example.

Headings of Contents

1 MySQL MONTH() Function
2 Extracting MONTH from the joining_date column
3 Using the MONTH() function with a NULL value
4 Summary

MySQL MONTH() Function

In MySQL, MONTH() is a date function that takes a date as a parameter and returns only the month part of the passed date. The returned value should be between 1 to 12. It works with DATE OR DATETIME column. It returns NULL if the passed date is NULL.

Syntax

The syntax of the MONTH() function is.

MONTH(date)

Let’s understand this MONTH() function with the help of the example.

Data

I have created a table named employee are having some columns like id, first_name, last_name, designation, and joining_date along with some demo records, As you can see below.

Now, I will apply the MONTH() function in the joining_date column to extract only the month part of the column value.

+----+------------+-----------+--------------------------+--------------+
| id | first_name | last_name | designation              | joining_date |
+----+------------+-----------+--------------------------+--------------+
|  1 | Vishvajit  | Rao       | Python Developer         | 2021-12-23   |
|  2 | Vinay      | Kumar     | Angular Developer        | 2020-08-22   |
|  3 | Minakshi   | Kumary    | SEO Specialist           | 2021-07-20   |
|  4 | John       | Doe       | Data Engineer            | 2020-11-06   |
|  5 | Akash      | Saini     | Junior Data Engineer     | 2022-01-30   |
|  6 | Harshita   | Singh     | Junior Accountant        | 2021-12-25   |
|  7 | Ayush      | Shukla    | Angular Developer        | 2021-08-20   |
|  8 | Abhishek   | Mishra    | Senior Data Engineer     | 2018-05-29   |
|  9 | Vaishanvi  | Mehta     | Senior SEO Specialist    | 2020-02-26   |
| 10 | Mahima     | Chaudhari | Junior Data Scientist    | 2021-03-23   |
| 11 | Arushi     | Shulka    | Junior Data Analyst      | 2021-05-29   |
| 12 | Mahesh     | Kumar     | Senior Python Developer  | 2020-03-22   |
| 13 | Ajay       | Gupta     | Senior Angular Developer | 2021-08-28   |
| 14 | Manish     | Das       | Senior HR Manager        | 2021-07-20   |
| 15 | David      | Russel    | Senior Data Analyst      | 2021-05-21   |
| 16 | Nishant    | Chahal    | Senior Accountant        | 2021-09-24   |
| 17 | Darshan    | Natrajan  | Hiring HR                | 2021-05-20   |
+----+------------+-----------+--------------------------+--------------+

Extracting MONTH from the joining_date column

To extract only the month part of the date or DateTime column, You need to pass the column inside MONTH() function, Le’s see.

Example:

In this example section, I am about to extract only those employees who had joined in 5th month.


mysql> SELECT
    ->     id, first_name, last_name, designation, joining_date
    -> FROM
    ->     employee
    -> WHERE
    ->     MONTH(joining_date) = 5;
+----+------------+-----------+----------------------+--------------+
| id | first_name | last_name | designation          | joining_date |
+----+------------+-----------+----------------------+--------------+
|  8 | Abhishek   | Mishra    | Senior Data Engineer | 2018-05-29   |
| 11 | Arushi     | Shulka    | Junior Data Analyst  | 2021-05-29   |
| 15 | David      | Russel    | Senior Data Analyst  | 2021-05-21   |
| 17 | Darshan    | Natrajan  | Hiring HR            | 2021-05-20   |
+----+------------+-----------+----------------------+--------------+
4 rows in set (0.00 sec)

As you can see in the above result set, We have successfully fetched only those employees who had joined in the 5th month.

I have written and executed the same SQL Query in the MySQL Workbench application, You can see below.

extract month from date in MySQL — **Using the month function in MySQL with an equal to the operator**

Example

Now, My requirement has been changed. The requirement is to select only those employees whose joining month is after the 5th month meaning after May. To fulfill this requirement, I have used the greater than operator ( > ).

mysql> SELECT
    ->     id, first_name, last_name, designation, joining_date
    -> FROM
    ->     employee
    -> WHERE
    ->     MONTH(joining_date) > 5;
+----+------------+-----------+--------------------------+--------------+
| id | first_name | last_name | designation              | joining_date |
+----+------------+-----------+--------------------------+--------------+
|  1 | Vishvajit  | Rao       | Python Developer         | 2021-12-23   |
|  2 | Vinay      | Kumar     | Angular Developer        | 2020-08-22   |
|  3 | Minakshi   | Kumary    | SEO Specialist           | 2021-07-20   |
|  4 | John       | Doe       | Data Engineer            | 2020-11-06   |
|  6 | Harshita   | Singh     | Junior Accountant        | 2021-12-25   |
|  7 | Ayush      | Shukla    | Angular Developer        | 2021-08-20   |
| 13 | Ajay       | Gupta     | Senior Angular Developer | 2021-08-28   |
| 14 | Manish     | Das       | Senior HR Manager        | 2021-07-20   |
| 16 | Nishant    | Chahal    | Senior Accountant        | 2021-09-24   |
+----+------------+-----------+--------------------------+--------------+
9 rows in set (0.00 sec)

As you can see in the above result, All the records have been displayed who had joined after the 5th month means after the month of May.

The same code has been written inside the MySQL Workbench application.

Example

In this example, I am about to select only those employees who joined in the 5th month of 2021.

To fulfill this condition, Firstly we have to use an additional function called YEAR(). The YEAR() function is responsible for extracting only the year from the date column because we will have to apply the condition of the year as well to filter the records.

mysql> SELECT
    ->     id, first_name, last_name, designation, joining_date
    -> FROM
    ->     employee
    -> WHERE
    ->     YEAR(joining_date) = 2021
    ->         AND MONTH(joining_date) = 5;
+----+------------+-----------+---------------------+--------------+
| id | first_name | last_name | designation         | joining_date |
+----+------------+-----------+---------------------+--------------+
| 11 | Arushi     | Shulka    | Junior Data Analyst | 2021-05-29   |
| 15 | David      | Russel    | Senior Data Analyst | 2021-05-21   |
| 17 | Darshan    | Natrajan  | Hiring HR           | 2021-05-20   |
+----+------------+-----------+---------------------+--------------+
3 rows in set (0.00 sec)

As you can see in the above result set, How we have successfully accessed the employee records who joined in 2021 in month 5th means May.

You can find here, I have executed the same code inside MySQL Workbench.

👉 You can learn more about MySQL Year Function:- Click Here

Example: Using MONTH() function with string date format

In this example, I am about to get the month from the string date format.

mysql> SELECT MONTH('2022-01-13') AS 'Month';
+-------+
| Month |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

Example: Using MONTH() function with date time string format

In this example, I have processed the month from string date time.

mysql> SELECT MONTH('2008-12-03 22:23:20') AS 'Month';
+-------+
| Month |
+-------+
|    12 |
+-------+
1 row in set (0.00 sec)

Using the MONTH() function with a NULL value

As we know, MONTH() will return a NULL value if the passed date is NULL.let’s see through an example.

mysql> SELECT MONTH(NULL) AS 'Month';
+-------+
| Month |
+-------+
|  NULL |
+-------+
1 row in set (0.00 sec)

👉 MySQL MONTH() Function Ref:- Click Here

Summary

So, In this article, we have seen how to extract month from date in MySQL with the help of a few examples. This function is mostly used in real-life projects when we want to perform some operations on only month in date or DateTime column.

I hope you found this article helpful, Please share and keep visiting for further MySQL interesting tutorials.

Have a nice day…

◀How to Extract Year from Date in MySQL

How to Get Last Day of the Month in MySQL▶