How to Get Month Name from Month Number in SQL Server

In this post, we want to discuss about a nice function to Get a month name from number in SQL Server. Before we get started, if you want to know about reduce log file size, please go through the following article: Query to reduce Transaction Log file size to just 1MB.

Introduction

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;

or

SELECT FORMAT(@date, 'MMMM', 'en-US') AS 'en-US', FORMAT(@date, 'MMMM', 'es-es') AS 'es-es'

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 weekday name or the month name if that’s what you require. Here’s an example:

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

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;

Get month name from number in SQL Server