When using SQL Server, you have a few different options when you need to return the month name from a date using T-SQL. By month name, I’m not talking about the month number (such as 07). I’m talking about the full name of the month (such as July).

For example, when given a date of 2018-07-01, you want July to be returned.

This article presents three ways to return the month name from a date in SQL Server using T-SQL.

The FORMAT() Function

The FORMAT() function returns a value formatted in the specified format and optional culture. You can use it to return the month name from a date.

Here’s an example:

DECLARE @date datetime2 = '2018-07-01';
SELECT FORMAT(@date, 'MMMM') AS Result;

Result:

+----------+
| Result   |
|----------|
| July     |
+----------+

In this case we provided a format string of MMMM which is a custom date and time format string for returning the month name.

The good thing about the FORMAT() function is that it accepts an optional argument that allows you to specify the culture to use.

Here are some examples:

DECLARE @date datetime2 = '2018-07-01';
SELECT 
    FORMAT(@date, 'MMMM', 'en-US') AS 'en-US',
    FORMAT(@date, 'MMMM', 'es-es') AS 'es-es',
    FORMAT(@date, 'MMMM', 'de-de') AS 'de-de',
    FORMAT(@date, 'MMMM', 'zh-cn') AS 'zh-cn';

Result:

+---------+---------+---------+---------+
| en-US   | es-es   | de-de   | zh-cn   |
|---------+---------+---------+---------|
| July    | julio   | Juli    | 七月    |
+---------+---------+---------+---------+

If you don’t provide the culture argument, the language of the current session is used. If you’re not sure what language the current session is using, here are 3 ways to get the language of the current session. If you find that you need to change the language, here’s how to set it to another language.

You can also use the FORMAT() function to get the abbreviated month name. To do this, simply use MMM (three Ms instead of four).

Here’s an example that returns both the abbreviated month name, and the full month name:

DECLARE @date datetime2(0) = '2028-09-01';
SELECT 
    FORMAT(@date, 'MMM', 'en-US') 'en-US',
    FORMAT(@date, 'MMM', 'es-es') 'es-es',
    FORMAT(@date, 'MMM', 'it') 'it'
UNION ALL
SELECT 
    FORMAT(@date, 'MMMM', 'en-US'),
    FORMAT(@date, 'MMMM', 'es-es'),
    FORMAT(@date, 'MMMM', 'it');

Result:

+-----------+------------+-----------+
| en-US     | es-es      | it        |
|-----------+------------+-----------|
| Sep       | sep.       | set       |
| September | septiembre | settembre |
+-----------+------------+-----------+

This is just one of many ways to get the abbreviated month name from a date (although this method is arguably the best). There are at least four more ways you can return the abbreviated month name from a date.

The DATENAME() Function

The DATENAME() function is similar to the DATEPART() function, except that it returns the name of the specified date part (but only where a name is applicable). That means it returns the week day name or the month name if that’s what you require.

Here’s how to get the month name with this function:

DECLARE @date datetime2 = '2018-07-01';
SELECT DATENAME(month, @date) AS Result;

Result:

+----------+
| Result   |
|----------|
| July     |
+----------+

The MONTHNAME() ODBC Scalar Function

There’s an ODBC scalar function specifically for returning the month name from a date. Its name is MONTHNAME(), and it goes like this:

DECLARE @date datetime2 = '2018-07-01';
SELECT {fn MONTHNAME(@date)} AS Result;

Result:

+----------+
| Result   |
|----------|
| July     |
+----------+

When using ODBC scalar functions in T-SQL, they are surrounded by curly braces ({}) and the function name is prefixed with fn.

If you need to get the short month name, see 5 Ways to Get the Short Month Name from a Date in SQL Server.