MySQL MONTH() Function

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. The following illustrates the syntax of the MONTH function:

MONTH(date);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, which represents January to December.

The MONTH() function returns NULL if the date is NULL. Also, it returns zero, if the date is zero (0000-00-00).

MySQL MONTH() function examples

Let’s take some examples of using the MONTH() function

1) Simple Month() function examples

The following example uses the MONTH() function to get the month of 2010-01-01:

SELECT MONTH('2010-01-01');Code language: JavaScript (javascript)

Output:

+---------------------+
| MONTH('2010-01-01') |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

To get the current month, you use the MONTH() function with the NOW() function as shown in the following statement:

SELECT MONTH(NOW()) CURRENT_MONTH;

Output:

+---------------+
| CURRENT_MONTH |
+---------------+
|            10 |
+---------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

How it works.

• First, use the NOW() function to get the current date and time.
• Second, pass the current date and time to the MONTH() function to get the current month.

The following shows how to use the MONTH function() with a zero date:

SELECT MONTH('0000-00-00');Code language: SQL (Structured Query Language) (sql)

It returns 0:

+---------------------+
| MONTH('0000-00-00') |
+---------------------+
|                   0 |
+---------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The following example uses MONTH function with a NULL date:

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

Output:

+-------------+
| MONTH(NULL) |
+-------------+
|        NULL |
+-------------+
1 row in set (0.00 sec)Code language: plaintext (plaintext)

2) Using the MONTH() function with table data

We’ll use the orders and orderdetails tables in the sample database:

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

SELECT 
  MONTH(orderDate) month, 
  ROUND(
    SUM(quantityOrdered * priceEach)
  ) subtotal 
FROM 
  orders 
  INNER JOIN orderdetails USING (orderNumber) 
WHERE 
  YEAR(orderDate) = 2004 
GROUP BY 
  month;Code language: SQL (Structured Query Language) (sql)

Output:

+-------+----------+
| month | subtotal |
+-------+----------+
|     1 |   292385 |
|     2 |   289503 |
|     3 |   217691 |
|     4 |   187576 |
|     5 |   248325 |
|     6 |   343371 |
|     7 |   325563 |
|     8 |   419327 |
|     9 |   283800 |
|    10 |   500234 |
|    11 |   979292 |
|    12 |   428838 |
+-------+----------+
12 rows in set (0.03 sec)Code language: plaintext (plaintext)

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

1. The INNER JOIN clause joins two tables orders and orderdetails using the orderNumber column.
2. The MONTH function is applied to the orderDate column to get the month of the orders.
3. The SUM() function calculates the subtotal of each line item in the sales order
4. The WHERE clause includes only the orders in 2004.
5. The GROUP BY clause groups the subtotal by month.

Summary

• UYse the MONTH() function to get the month of a specific date.