In SQL Server, you can use the MONTH() function to return the “month” part of a date. This is returned as an integer (not the month name).
Below are examples of how to use this function.
Syntax
The syntax goes like this:
MONTH ( date )
Where date is an expression that resolves to one of the following data types:
- date
- datetime
- datetimeoffset
- datetime2
- smalldatetime
- time
This can be a column expression, expression, string literal, or user-defined variable.
Example
Here’s a basic example of how it works:
SELECT SYSDATETIME() AS 'Date', MONTH(SYSDATETIME()) AS 'Month';
Result:
+-----------------------------+---------+ | Date | Month | |-----------------------------+---------| | 2018-06-18 00:39:06.7954314 | 6 | +-----------------------------+---------+
So the MONTH() function was able to extract the month from the datetime2 value (which was returned by the SYSDATETIME() function).
Date Provided as a String Literal
Here’s an example where the date is provided as a string literal.
SELECT MONTH('2019-01-07') AS Result;
Result:
+----------+ | Result | |----------| | 1 | +----------+
And here’s an example where the date is provided in a different format:
SELECT MONTH('07/01/2017') AS Result;
Result:
+----------+ | Result | |----------| | 7 | +----------+
However, it’s usually best to avoid using dates in such formats. If you must do so, you’ll need to be mindful of the language settings and/or the date format settings of the current session.
Language Settings
The output of the previous example will depend on the language settings and/or date format settings of the current session.
When we set the language, the date format is implicitly set at the same time.
Here’s what happens when we provide the same date argument in two different language environments.
British
SET LANGUAGE British;
SELECT MONTH('07/01/2017') AS Result;
Result:
+----------+ | Result | |----------| | 1 | +----------+
us_English
SET LANGUAGE us_English;
SELECT MONTH('07/01/2017') AS Result;
Result:
+----------+ | Result | |----------| | 7 | +----------+
Date Format Settings
The date format settings can override the language settings, so you also need to be aware of this setting. For example, we could be using us_English for our language (which has a default date format of mdy), but we could override the date format to be dmy.
Here’s an example:
us_English – Default Date Format
Here, we set the language to us_English, which implicitly sets the date format to myy.
SET LANGUAGE us_English;
SELECT MONTH('07/01/2017') AS Result;
Result:
+----------+ | Result | |----------| | 7 | +----------+
us_English – Override Date Format
Here, we set the language to us_English (which implicitly sets the date format), but then we explicitly set the date format to dmy. This overrides the date format that was implicitly set when we set the language.
SET LANGUAGE us_English;
SET DATEFORMAT dmy;
SELECT MONTH('07/01/2017') AS Result;
Result:
+----------+ | Result | |----------| | 1 | +----------+
Return the Month Name
If you need to return the month name (as opposed to the month number), see 3 Ways to Get the Month Name from a Date in SQL Server.