Summary: in this tutorial, you will learn how to use the MySQL MONTH function to get the month of a given date.

Introduction to MySQL MONTH function

The MONTH function returns an integer that represents the month of a specified date value. The following illustrates the syntax of the MONTH function:

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

The MONTH function accepts one argument which is a DATE or DATETIME value. It returns an integer that ranges from 1 to 12 for January to December.

If you pass a zero date e.g., 0000-00-00, the MONTH function returns 0. In case you the date is NULL, the MONTH function returns NULL.

MySQL MONTH function examples

The following example shows how to get the month of 2010-01-01:

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

To get the current month, you use the following statement:

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

In this example, we passed the result of the NOW function, which is the current date and time, to the MONTH function to get the current month.

As mentioned earlier, the MONTH function returns 0 for a zero date as shown in the following example:

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

The MONTH function returns NULL if the input date value is NULL:

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

Practical usages of MySQL MONTH function

See the following orders and orderdetails tables in the sample database:

The following statement gets the order’s volume by month in 2004:

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

The following is the output of the query:

Let’s break the statement into smaller parts to make it easier to understand:

1. The INNER JOIN clause joined two tables orders and orderdetails using the orderNumber column.
2. The MONTH function is applied on the orderDate column to get the month data
3. The SUM function calculated the subtotal of each line item in the sales order
4. The WHERE clause filtered only orders whose order dates in 2004.
5. The GROUP BY clause groups the subtotal by month.

In this tutorial, you have learned how to use the MySQL MONTH function to get a month of a specified date.