How to use the MySQL MONTH() function

In this article, we will learn how to use the MySQL MONTH() function, which returns the month of a given date as an integer value from 1 to 12. We will also see some examples of how to use this function in different scenarios, and explore some related functions that can be useful for working with dates and months.

Syntax

The syntax of the MONTH() function is as follows:

MONTH(date)

The date parameter can be any valid date or datetime expression, or a string that can be converted to a date or datetime value. If the date parameter is invalid or NULL, the function returns NULL.

Examples

Let’s see some examples of how to use the MONTH() function in MySQL.

Example 1: Get the current month

We can use the MONTH() function with the CURDATE() function, which returns the current date, to get the current month as an integer value. For example:

SELECT MONTH(CURDATE()) AS current_month;

This query will return the current month as an integer value. For example, if the current date is 2023-12-15, the query will return 12.

Example 2: Get the month name from a date

We can use the MONTH() function with the MONTHNAME() function, which returns the name of the month for a given date, to get the month name from a date. For example:

SELECT MONTHNAME('2023-04-06') AS month_name;

This query will return the name of the month for the given date. In this case, the query will return ‘April’.

Example 3: Get the number of records for each month

We can use the MONTH() function with the GROUP BY clause to get the number of records for each month in a table. For example, suppose we have a table called orders that stores the order details of a online store, with the following columns:

order_id: the unique identifier of the order
customer_id: the unique identifier of the customer who placed the order
order_date: the date and time when the order was placed
order_amount: the total amount of the order

We can use the following query to get the number of orders for each month in the year 2023:

SELECT MONTH(order_date) AS month, COUNT(*) AS order_count
FROM orders
WHERE YEAR(order_date) = 2023
GROUP BY month
ORDER BY month;

This query will return the month and the order count for each month in the year 2023, ordered by the month. For example, the query might return something like this:

month	order_count
1	25
2	32
3	28
4	35
5	30
6	27
7	33
8	31
9	29
10	34
11	36
12	38

There are some other functions that are related to the MONTH() function, and can be useful for working with dates and months. Here are some of them:

MONTHNAME(): This function returns the name of the month for a given date. For example, MONTHNAME('2023-01-01') returns ‘January’.
MONTH_FORMAT(): This function returns the month of a date formatted according to a specified format. For example, MONTH_FORMAT('2023-01-01', '%b') returns ‘Jan’.
LAST_DAY(): This function returns the last day of the month for a given date. For example, LAST_DAY('2023-02-15') returns ‘2023-02-28’.
DAY(): This function returns the day of the month for a given date as an integer value from 1 to 31. For example, DAY('2023-01-15') returns 15.
DAYNAME(): This function returns the name of the weekday for a given date. For example, DAYNAME('2023-01-01') returns ‘Sunday’.
DAYOFWEEK(): This function returns the weekday index for a given date as an integer value from 1 (Sunday) to 7 (Saturday). For example, DAYOFWEEK('2023-01-01') returns 1.
DAYOFYEAR(): This function returns the day of the year for a given date as an integer value from 1 to 366. For example, DAYOFYEAR('2023-01-01') returns 1.

Conclusion

In this article, we learned how to use the MySQL MONTH() function, which returns the month of a given date as an integer value from 1 to 12. We also saw some examples of how to use this function in different scenarios, and explored some related functions that can be useful for working with dates and months.