How to display month name in SQL Server?

How to display the full month name, i.e. 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, 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 for formatting, you can use this function to get the month name in letters, especially when dealing with different languages or locales.

So 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. Another function that you may encounter with ODBC is the MONTHNAME function. It’s worth noting that while MONTHNAME() is an ODBC function. But MONTHNAME it’s not natively supported in T-SQL for SQL Server.

1. SQL Server query to display the month name

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.

To sum up, this short tutorial explains how to display the full month name in all letters from a SQL date. Another post about date and time shows how to calculate a date difference with the hours, minutes and seconds.

2. Display month name in all letters 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. Month name abbreviation in SQL Server

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 month in 10 most spoken languages in the world

Languages are a significant part of human history, culture, and communication. Different languages offer unique insights into diverse worldviews and traditions. The following are ten of the most widely spoken languages around the world. Each language is accompanied by a T-SQL code snippet that formats a date to display the month name in that specific language. So, let’s break down the T-SQL code by language with a short description for each language.

SQL Server month name in Chinese (Mandarin)

• Description: Official language of China and Taiwan.

DECLARE @date datetime = '2023-01-01'; SELECT FORMAT(@date, 'MMMM', 'zh-CN') AS 'zh-CN'; -- Chinese (Mandarin)

Spanish

• Description: Spoken in many countries, especially in Latin America and Spain.

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

English

• Description: A global lingua franca.

DECLARE @date datetime = '2023-01-01'; SELECT FORMAT(@date, 'MMMM', 'en-US') AS 'en-US'; -- English American

Hindi

• Description: Predominantly spoken in India.

DECLARE @date datetime = '2023-01-01'; SELECT FORMAT(@date, 'MMMM', 'hi-IN') AS 'hi-IN'; -- Hindi

Month name in Arabic using T-SQL

• Description: Official language in 26 countries in the Middle East and Africa.

DECLARE @date datetime = '2023-01-01'; SELECT FORMAT(@date, 'MMMM', 'ar-SA') AS 'ar-SA'; -- Arabic

Portuguese

• Description: Official language of Brazil and Portugal.

DECLARE @date datetime = '2023-01-01'; SELECT FORMAT(@date, 'MMMM', 'pt-BR') AS 'pt-BR'; -- Portuguese (Brazil)

Bengali

• Description: Official language in Bangladesh and parts of India.

DECLARE @date datetime = '2023-01-01'; SELECT FORMAT(@date, 'MMMM', 'bn-BD') AS 'bn-BD'; -- Bengali (Bangladesh)

Russian

• Description: Official language of Russia.

DECLARE @date datetime = '2023-01-01'; SELECT FORMAT(@date, 'MMMM', 'ru-RU') AS 'ru-RU'; -- Russian

Japanese

• Description: Official language of Japan.

DECLARE @date datetime = '2023-01-01'; SELECT FORMAT(@date, 'MMMM', 'ja-JP') AS 'ja-JP'; -- Japanese

Month name in Lahnda (Western Punjabi)

• Description: Spoken primarily in the Punjab region of Pakistan.

DECLARE @date datetime = '2023-01-01'; SELECT FORMAT(@date, 'MMMM', 'pa-PK') AS 'pa-PK'; -- Punjabi (Pakistan)

5. Query to display month name in top 10 languages

The code below, still uses the SQL Server’s FORMAT() function to extract the month name from a given date, here it is 2023-12-31. It generates a result set that displays the month’s name for this date across multiple prominent languages, such as Chinese (Mandarin), Spanish, English, and several others, all within a single query.

-- ** Code by Expert-Only.com ** --
DECLARE @date datetime = '2023-12-31';

SELECT 
    FORMAT(@date, 'MMMM', 'zh-CN') AS 'zh-CN',  -- Chinese (Mandarin)
    FORMAT(@date, 'MMMM', 'es-ES') AS 'es-ES',  -- Spanish
    FORMAT(@date, 'MMMM', 'en-US') AS 'en-US',  -- English American
    FORMAT(@date, 'MMMM', 'hi-IN') AS 'hi-IN',  -- Hindi
    FORMAT(@date, 'MMMM', 'ar-SA') AS 'ar-SA',  -- Arabic
    FORMAT(@date, 'MMMM', 'pt-BR') AS 'pt-BR',  -- Portuguese (Brazil)
    FORMAT(@date, 'MMMM', 'bn-BD') AS 'bn-BD',  -- Bengali (Bangladesh)
    FORMAT(@date, 'MMMM', 'ru-RU') AS 'ru-RU',  -- Russian
    FORMAT(@date, 'MMMM', 'ja-JP') AS 'ja-JP',  -- Japanese
    FORMAT(@date, 'MMMM', 'pa-PK') AS 'pa-PK';  -- Punjabi (Pakistan);

The result gives something like this in SSMS:

Note the specificities for Arabic, the month name “جمادى الثانية” is not December in Arabic; it refers to a month in the Islamic lunar calendar. And in the Gregorian calendar, December is represented as “ديسمبر” in Arabic.

6. T-SQL query to display month number

To display the month number in SQL Server, use the DATEPART() function with the MONTH keyword. This returns the month as an integer, like 1 for January or 12 for December. This method is useful for calculations or sorting.

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

Conclusion on how to display month name in 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.

By the way, we’d like to hear from you! Please do not hesitate to leave a comment or to contact us.