SQL MONTH Function Use and Examples

Overview

The MONTH function returns the month part of the date as an integer from the date or datetime provided.

Explanation

Syntax

MONTH(date)

Parameters

• Date – Is the date or datetime provided

Simple MONTH Example

The following example will show the month of March 9, 2021.

SELECT MONTH('3-9-2021 5:00:55 PM') as MONTH

MONTH function with NULL values

If the parameter of the MONTH function is NULL, a NULL value will be returned.

SELECT MONTH(NULL) as MONTH

Conversion failed error for the MONTH function

A typical error will occur if the date is invalid. The following example is using a wrong month number (13).

SELECT MONTH('13-02-2021 5:00:55 PM') as MONTH

The error message displayed is as follows:

Conversion failed when converting date and/or time from character string.

Using Time with MONTH function

If the MONTH function receives a time parameter, the value returned is 1.

SELECT MONTH('5:00:55 PM') as MONTH

MONTH function with Table Data

The following example will show the month of the StartDate.

SELECT StartDate, MONTH(StartDate) as MONTH
FROM [Production].[ProductCostHistory]

Additional Information

• SQL DAY
• Mimic timestamp behavior of other database platforms to store last modified date
• SQL Convert Date to YYYYMMDD
• SQL Server DIFFERENCE Function
• SQL Server CONCAT Function
• Format SQL Server Dates with FORMAT Function
• DATEDIFF SQL Server Function

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 10 years of experience as a QE and developer for SQL Server related software. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs.

• MSSQLTips Awards: Author of the Year Contender – 2015-2018, 2022, 2023 | Champion (100+ tips) – 2018