SQL Server Get Month From Date

by

In this comprehensive article, I’ll provide real-time examples of multiple approaches to extracting the month from a date in SQL Server. This is one of the requirements that I recently got for one of my applications.

SQL Server Get Month From Date

Let us discuss all the possible approaches individually.

Approach 1: Using the MONTH() Function

The simplest and most direct way to extract the month from a date in SQL Server is by using the built-in MONTH() function. This function returns an integer from 1 to 12 representing the month.

Syntax

MONTH(date)

Example

This query returns the sale date and its corresponding month number for all sales orders placed in 2025 using the following query.

SELECT 
    Sale_Date,
    MONTH(Sale_Date) AS SaleMonth
FROM 
    Sales
WHERE 
    YEAR(Sale_Date) = 2025;

After executing the above query, I got the expected output as shown in the screenshot below.

SQL Server Get Month From Date

Approach 2: Using DATEPART() Function

We can also use the DATEPART() function for this purpose.

Syntax

DATEPART(month, date)

Example

SELECT 
    Sale_Date,
    DATEPART(month, Sale_Date) AS SalesOrderMonth
FROM 
    Sales
WHERE 
    DATEPART(year, Sale_Date) = 2025;

After executing the above query, I got the expected output as shown in the screenshot below.

sql server get month and year from date

The MONTH() function and DATEPART(month, date) return the same value, but DATEPART offers more flexibility since you can extract other date parts with the same function.

Method 3: Using the FORMAT() Function (SQL Server 2012 and Later)

If you’re using SQL Server 2012 or later, you can also use the FORMAT() function.

Syntax:

FORMAT(date, format_string)

Example:

SELECT 
    Sale_Date,
    FORMAT(Sale_Date, 'MM') AS MonthNumber,
    FORMAT(Sale_Date, 'MMMM') AS MonthName
FROM 
    Sales;

This returns:

  • ‘MM’ – Two-digit month (01-12)
  • ‘MMMM’ – Full month name (January-December)

After executing the above query, I got the expected output as shown in the screenshot below.

get month from date in sql

Method 4: Using the DATENAME() Function

When you specifically need the month name rather than its number, the DATENAME() function is ideal.

Syntax

DATENAME(month, date)

Example

SELECT 
    Sale_Date,
    DATENAME(month, Sale_Date) AS SaleMonth
FROM 
    SALES
ORDER BY 
    MONTH(Sale_Date);

After executing the above query, I got the expected output as shown in the screenshot below.

How To Get Month Name From Date In SQL Server

Approach 5: Month Extraction for Grouping Data

Example: Monthly Sales Report

SELECT 
    YEAR(Sale_Date) AS SaleYear,
    MONTH(Sale_Date) AS SaleMonth,
    SUM(sale_Amount) AS TotalSales
FROM 
    Sales
GROUP BY 
    YEAR(Sale_Date),
    MONTH(Sale_Date)
ORDER BY 
    SaleYear,
    SaleMonth;

This query generates a monthly sales report, showing total sales for each month across different years. After executing the above query, I got the output as expected, as shown in the screenshot below.

How To Get Previous Month Data From Current Date In SQL Server

Performance Considerations

Here are some performance tips for the month extraction.

  1. Indexing: Ensure the date column is indexed correctly if you frequently filter or sort by month.
  2. Function Impact: Using functions like MONTH() or DATEPART() on indexed columns in WHERE clauses can prevent SQL Server from using indexes efficiently.
  3. Method Selection: For pure performance, MONTH() and DATEPART() are generally faster than FORMAT(), which has more overhead due to string conversion.

Conclusion

Extracting the month from a date in SQL Server is so easy using the simple MONTH() function and the other approaches as explained in this article.

You may also like to follow the articles below