MONTH Function in SQL Server

In this SQL Server tutorial, you will learn about the MONTH function in SQL Server, which helps categorise the information based on the month part of the date.

You will understand the syntax of the MONTH() function and just a small example of where you can use this function. Then, apply the MONTH() function to the literal date value.

After that, you will understand what happens when you pass only the time value to the MONTH() function. Finally, you will use the MONTH() function with a table.

Table of Contents

If you have a table related to sales and want to know the total sales by month, then you can use the MONTH() function in your query to get the month part of the specified date and compute the monthly sales.

So, using the MONTH function in SQL Server, you can sort the given data based on the month. The syntax is given below.

MONTH(date);

Where,

date: It is the actual date whose month you want to extract.

The return type of the MONTH() function is integer, which means when this function is applied on the date, it returns the month part as a number.

For example, you have the date ‘2024-01-01’, and to extract the month part of this date, you can use the below query.

SELECT MONTH('2024-01-01') AS MonthPart;

The month part of the date ‘2024-01-01’ is the 1, which is the January. It is easy to extract the month part of the date using the MONTH function in SQL Server.

Above is a simple example of using the MONTH() function to get the month of the literal date value. Let’s move and see what other ways MONTH() can be used to obtain information based on the month part.

This is how to use the MONTH function in SQL Server to get the month of the given date.

MONTH Function in SQL Server with Time Value Only

You can also pass the timestamp value to the MONTH() function that only contains the time part, which is what will happen if you pass the time value to the MONTH() function, which means what the MOTN() function will return in those cases.

For example, suppose you have the time ’10:30:40′, which is 10 hours, 30 minutes and 40 seconds. Let’s apply the MONTH() function to this time value.

SELECT MONTH('10:30:40') AS MonthPart;

MONTH Function in SQL Server with Time Value Only

We get the month part as 1 when only the time value is provided to the MONTH() function, as you can see in the output of the query, so when passing only the time date to the MONTH() function, then it returns only the base month which 1 in this case.

This is how to use the MONTH function in SQL Server with time value.

MONTH Function in SQL Server with Table

Let’s see how to use the MONTH() function on the table to fetch the information based on the month.

For example, we have a sales table with columns product_name, sale_date, and the sale_amount shown below.

MONTH Function in SQL Server with Sales Table

Look at the sale_date column here to compute the total sales by month. Now, use the below query to learn how to apply the MONTH() function on the table to get meaningful monthly information.

SELECT MONTH(sale_date) AS Month, SUM(sale_amount) MonthTotalSales 
FROM sales
GROUP BY MONTH(sale_date);

From the above output, the result set contains the two columns ‘Month’ representing the month number and ‘MonthTotalSales’ showing the total sales in each month. For example, total sales in the 1st month (January) is 225075$.

First, in the query part, extract the month number from the ‘sale_date’ column using the MONTH(sale_date), then group the rows based on the month number using the GROUP BY MONTH(sale_date).

Finally, the total sale is computed using the SUM(sale_amount) based on the created month group.

This is how to use the MONTH() function in SQL Server to extract the month part of the given date.

Conclusion

In this SQL Server tutorial, you covered how to use the MONTH() function to extract the month part of the specified date, then learned that the MONTH() function returns the month as 1 when the time value is passed to it. Finally, you apply the month function on the table to compute the total monthly sales.