How To Display Month Name In SQL Server ?

To display the full month name in all letters from a date using a SQL Server query?

The SQL Server database offers T-SQL methods to display the month name in diverse formats, including name in all letters, abbreviations, and in multiple languages. The DATENAME() function, combined with the MONTH keyword for example, is commonly used to fetch and format month names. Indeed, the 2 native functions: DATENAME() and FORMAT(), are the primary recommended methods to use in native T-SQL to get the month’s name in letters.

Indeed, in T-SQL, there are a few functions you can use to display the month name in letters:

1. DATENAME(): This function returns a character string representing the specified datepart of the specified date.
2. FORMAT(): While primarily designed for formatting, you can use this function to get the month name in letters, especially when dealing with different languages or locales.

1. T-SQL query to display the month name from the current date

The query below simply shows the month name using the MONTH option and the current timestamp with the default output format.

SELECT DATENAME(MONTH, GETDATE() ) AS [MonthName],
       GETDATE() AS [CurrentDate];

For example, it returns “June” in all letters for today.

Another post from the blog is about date and time and shows how to calculate a date difference with the hours, minutes and seconds.

2. Get month name in all letters from a date in SQL Server

One way to display the month name in SQL Server is by showing the full month name in English. This method can be helpful in situations where you need to display the month name in a more formal manner, such as in a report or a document. By using the DATENAME() function with the MONTH keyword and the FORMAT() function with the ‘MMMM’ specifier, you can easily retrieve the full month name in English. This query simply returns the full month name, such as “January” or “December”.

SELECT FORMAT(GETDATE(), 'MMMM') AS [FullMonthName];

3. Display the month name abbreviation with T-SQL

To display the month abbreviation in SQL Server, you can utilize the DATENAME() function paired with the MONTH keyword or the FORMAT() function using the ‘MMM’ specifier. This method, producing results like “Jan” or “Dec”, is particularly beneficial for concise representations in tables or charts. It’s also advantageous when dates need to be grouped by month, offering a succinct categorization. Below is the illustrative code example to achieve this:

SELECT FORMAT(DATENAME(MONTH, GETDATE()), 'MMM') AS [MonthAbbreviation];

4. Display the month number using T-SQL DATEPART function

This time, to do the opposite operation, i.e. display the month number in SQL Server, use the DATEPART() function with the MONTH keyword. This returns the month as an integer, from 1 for January to 12 for December. This method is useful for calculations or also sorting data in result queries for example.

SELECT DATEPART(MONTH, GETDATE()) AS [MonthNumber];

Different ways to display the month name using SQL Server

In SQL Server, various built-in functions enable you to display month names in different formats, including full names, abbreviations, or numerical representations. The FORMAT() function allows for language-specific month naming. These tools are invaluable for reporting, charting, or month-based calculations.

This guide provides insights into showcasing month names effectively in SQL Server. For further precision, like calculating the exact time difference between two dates, refer to our tutorial on date difference in SQL Server. Another common challenge is to calculate the time difference between two dates, with accuracy, i.e. including the hours, minutes, seconds and sometime milliseconds.

How to Calculate Difference Between Two Dates in T-SQL ?

Display the month name is different languages

To go further and learn how to display the month name in different languages, check out this T-SQL query to get the month in the 10 major languages worldwide. Below is an overview of how to build such a query using the FORMAT function.

DECLARE @date datetime = '2023-01-01'; SELECT FORMAT(@date, 'MMMM', 'es-ES') AS 'es-ES'; -- Spanish

Please do not hesitate to leave a comment or to contact us.