When working with dates in SQL, one of the most essential functions is MONTH(). It allows us to extract the month from a given date, making it super useful for reporting, filtering, and grouping. In this article, I’ll dive into how MONTH() works in SQL and provide some of the best practical examples.
What Is the MONTH() Function in SQL?
The MONTH() function retrieves the month as an integer (from 1 to 12) from a given date. This is particularly helpful when working with date-based queries where you need to filter or aggregate data by month.
MONTH() Syntax
The syntax for using MONTH() is straightforward:
MONTH(date_expression)Here, date_expression can be a column of type DATE, DATETIME, or even a manually provided date.
Basic Example of the MONTH() Function
Let’s start with a simple example where we extract the month from a specific date:
SELECT MONTH('2024-06-15') AS month_number;The output will be:
| month_number |
|---|
| 6 |
As expected, the function returns 6 because the given date falls in June.
Using MONTH() on a Table Column
We can also use MONTH() on a column from an existing table:
SELECT order_id, order_date, MONTH(order_date) AS order_month
FROM orders;This query will return:
| order_id | order_date | order_month |
|---|---|---|
| 101 | 2024-02-14 | 2 |
| 102 | 2024-06-20 | 6 |
Filtering Records by a Specific Month
If we need to find all orders placed in June, we can use a WHERE clause with MONTH():
SELECT * FROM orders
WHERE MONTH(order_date) = 6;This retrieves all records where the month part of order_date equals 6.
Grouping Data by Month
The MONTH() function is especially useful when grouping data. For example, if we want to count orders per month:
SELECT MONTH(order_date) AS month, COUNT(*) AS total_orders
FROM orders
GROUP BY MONTH(order_date)
ORDER BY month;This can help in analyzing order trends over different months.
Sorting by Month Correctly
One might think that sorting by month is as easy as:
SELECT order_id, order_date
FROM orders
ORDER BY MONTH(order_date);However, this only sorts by month disregarding the year. If you need chronological sorting, ensure that you also order by year:
SELECT order_id, order_date
FROM orders
ORDER BY YEAR(order_date), MONTH(order_date);Handling NULL Values in MONTH()
If a date column contains NULL values, MONTH() will return NULL:
SELECT MONTH(NULL) AS month_value;This results in:
| month_value |
|---|
| NULL |
To avoid NULL issues, you can use COALESCE():
SELECT COALESCE(MONTH(order_date), 0) FROM orders;Alternative Methods to Extract the Month
Besides MONTH(), there are other ways to extract month values:
DATEPART(MONTH, date_expression)– Works in SQL Server.EXTRACT(MONTH FROM date_expression)– Works in PostgreSQL and MySQL.TO_CHAR(date_expression, 'MM')– Returns the month as a string in PostgreSQL and Oracle.
Conclusion
Using the MONTH() function in SQL simplifies extracting, filtering, and grouping data by month. Whether you’re working with reports or creating time-based queries, understanding how MONTH() works in SQL is essential. Keep these examples in mind, and you’ll navigate date-based queries like a pro.
Other interesting article:
How YEAR works in SQL? Best YEAR examples