SQL Server MONTH Function

Summary: in this tutorial, you will learn how to use the SQL Server MONTH() function to extract the month from a date.

SQL Server MONTH() function overview

The MONTH() function returns an integer value which represents the month of a specified date.

The following shows the syntax of the MONTH() function:

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

The MONTH() function takes an argument which can be a literal date value or an expression that can resolve to a TIME, DATE, SMALLDATETIME, DATETIME, DATETIME2, or DATETIMEOFFSET value.

The MONTH() function returns the same value as the following DATEPART() function:

DATEPART(month,input_date)
Code language: SQL (Structured Query Language) (sql)

SQL Server MONTH() function examples

A) Using MONTH() function with a literal date value

This example uses the MONTH() function to extract a month from the date '2020-12-01':

SELECT 
	MONTH('2020-12-01') [month];
Code language: SQL (Structured Query Language) (sql)

Here is the output:

month
-----------
12

(1 row affected)

B) Using MONTH() function with a date value that has only time data

The MONTH() function will return 1 if the date value contains only time part:

SELECT 
	MONTH('15:30:20') [month];
Code language: SQL (Structured Query Language) (sql)

The output is as follows:

month
-----------
1

(1 row affected)

C) Using MONTH() function with table columns example

We will use the sales.orders and sales.order_items from the sample database for demonstration.

This example uses the MONTH() function to extract the month data from the values in the shipped_date column. It returns the gross sales by month in 2018 using the SUM() function and GROUP BY clause:

SELECT MONTH(shipped_date) [month], 
    SUM(list_price * quantity) gross_sales
FROM sales.orders o
    INNER JOIN sales.order_items i ON i.order_id = o.order_id
WHERE shipped_date IS NOT NULL
    AND YEAR(shipped_date) = 2017
GROUP BY MONTH(shipped_date)
ORDER BY [month];
Code language: SQL (Structured Query Language) (sql)

The following picture shows the output:

In this tutorial, you have learned how to extract the month from a specified date by using the SQL Server MONTH() function.