Sometimes when working with SQL Server (or any other DBMS for that matter), you need to return the short name for a month. By this I mean the 3 letter abbreviation of a month. For example, you need “Dec” instead of “December”.

Here are four ways you can extract the shortened month name from a date in SQL Server.

The FORMAT() Function

The FORMAT() function has been available since SQL Server 2012, and it’s the most concise way of returning the month as a 3 letter abbreviation.

Here’s an example of how it works:

DECLARE @date datetime2 = '2000-01-01';
SELECT FORMAT(@date, 'MMM') AS 'FORMAT';

Result:

FORMAT
------
Jan   

The first line simply declares a variable and assigns a date to it. The second line is where we return the short month name from the date.

The CAST() and DATENAME() Functions

This option casts the date as CHAR(3), thereby cutting off any characters that follow the first three.

Here’s an example:

DECLARE @date datetime2 = '2000-01-01';
SELECT CAST(DATENAME(month, @date) AS CHAR(3)) AS 'CAST/DATENAME';

Result:

CAST/DATENAME
-------------
Jan          

The LEFT() and DATENAME() Functions

This option is similar to the previous one, except that it uses the LEFT() function to take the 3 leftmost characters from the date.

Example:

DECLARE @date datetime2 = '2000-01-01';
SELECT LEFT(DATENAME(month, @date), 3) AS 'LEFT/DATENAME';

Result:

LEFT/DATENAME
-------------
Jan          

The LEFT() and CONVERT() Functions

This option converts the date to varchar, then takes the first three characters.

Example:

DECLARE @date datetime2 = '2000-01-01';
SELECT LEFT(CONVERT(varchar, @date, 100), 3) AS 'LEFT/CONVERT';

Result:

LEFT/CONVERT
------------
Jan         

In this example, the 100 argument styles the date so that it’s in the following format: mon dd yyyy hh:miAM (or PM). So in our case, the date is styled as Jan 1 2000 12:00AM.

From there, it’s simply a matter of snipping off the first three letters with the LEFT() function.

The LEFT() and MONTHNAME() Functions

This option uses the MONTHNAME() ODBC scalar function to return the month name. And as with the previous two examples, we simply extract the first three letters of that month name.

Example:

DECLARE @date datetime2 = '2000-01-01';
SELECT LEFT({fn MONTHNAME(@date)}, 3) AS 'LEFT/MONTHNAME';

Result:

LEFT/MONTHNAME
--------------
Jan