SQL Server MONTH() - Get Month as Integer

In SQL Server, the MONTH() function returns the month as an integer from the specified date. It returns 1 for January, 2 for February, and so on.

MONTH(date)

Parameters

date: An expression that can resolve to a date, datetime, datetime2, time, smalldatetime, or datetimeoffset value. The date parameter can be a string literal, a user-defined variable, or a table column.

Return Value

Returns an integer that represents the month part of the specified date. It returns the same value as DATEPART(month, date) function.

If the specified date contains only the time part, then the MONTH() function returns 1.

Get Month as Integer

In the following example, the MONTH() function returns a month as an integer from the specified date string literal in 'MM/dd/yyyy' format.

SELECT MONTH ('11/23/2022') AS Month

The MONTH() function returns 1 if you pass a time string value.

SELECT MONTH('10:22:15') AS Result

You can use the MONTH() function with the column of a table. For example, the following gets the month from the HireDate column of the Employee table.

SELECT EmployeeID, FirstName, HireDate, MONTH(HireDate) AS HireMonth FROM Employee

Get the Month Name from Date

To get the month name from a date, use the DATENAME() function.

In the below example, we have just the month as integer returned from the MONTH() function. To get the month name from the month integer, you need to CAST the month as INT and CONCAT it with the year and day part(we have used 1900 for year and 01 for day) to get the date and use the DATENAME function on this date.

Note: You can create a function in SQL Server which takes the month as integer and returns month as name.

SELECT DATENAME (mm, CONCAT('1900', FORMAT(CAST(MONTH('11/23/2022') AS INT), '00'), '01')) AS MonthName