MySQL MONTH Function

 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 the function returns an integer that represents the month of a specified date value. The following illustrates the syntax of the MONTH function:

MONTH(date);

The MONTH the function accepts one argument which is a DATE or DATETIME value. It returns an integer that ranges from 1 to 12 from 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:

mysql> SELECT MONTH('2010-01-01');
+---------------------+
| MONTH('2010-01-01') |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.00 sec)

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

mysql> SELECT MONTH(NOW()) CURRENT_MONTH;
+---------------+
| CURRENT_MONTH |
+---------------+
|             7 |
+---------------+
1 row in set (0.00 sec)

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 the function returns 0 for a zero date as shown in the following example:

mysql> SELECT MONTH('0000-00-00');
+---------------------+
| MONTH('0000-00-00') |
+---------------------+
|                   0 |
+---------------------+
1 row in set (0.00 sec)

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

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

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:

SELECT 
    MONTH(orderDate) month,
    ROUND(SUM(quantityOrdered * priceEach)) subtotal
FROM
    orders
        INNER JOIN
    orderdetails USING (orderNumber)
WHERE
    YEAR(orderDate) = 2004
GROUP BY month;

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 the function is applied to the orderDate column to get the monthly 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.