SQL Server: Get Month Name in Different Languages

How to get month name from date in SQL Server? If we use English we can simply use one of these queries:

SELECT GETDATE() 'Today', DATENAME(month,GETDATE()) 'Month Name'

SELECT GETDATE() 'Today', DATENAME(mm,GETDATE()) 'Month Name'

SELECT GETDATE() 'Today', DATENAME(m,GETDATE()) 'Month Name'

Result:

We can also use SET LANGUAGE to display the month name in specific other languages. But it must be an official language that exists in the sys.syslanguages (Transact-SQL).

SET LANGUAGE Arabic
SELECT DATENAME(mm, GETDATE()) 'Arabic Month Name'

SET Language Russian
SELECT DATENAME(mm, GETDATE()) 'Russian Month Name'

--mengembalikan setting bahasa ke inggris
SET LANGUAGE English
SELECT DATENAME(mm, GETDATE()) 'English Month Name'

Change setting will be applied to the relevant session. If you want it back to English in the same session, then execute the "SET LANGUAGE English" again.

Unfortunately, not all languages are included in the list. For example, Indonesian (Bahasa Indonesia) is not on the list.

So how to get the month name in a language that is not on the list? You need to create your own function that contains each month's name definition. 

The below sample is for Indonesian, you can change it to the language you want.

CREATE FUNCTION [dbo].[udfGetMonthName] (@iMonth INT)  
/* This function returns name of month by number in Indonesian
    Author: rIrsan. (6/14/22)
*/
RETURNS VARCHAR(20) AS  
BEGIN 
DECLARE @MonthName AS VARCHAR(20)
SET @MonthName = CASE WHEN @iMonth = 1 THEN 'Januari'
                      WHEN @iMonth = 2 THEN 'Februari'
      WHEN @iMonth = 3 THEN 'Maret'
      WHEN @iMonth = 4 THEN 'April'
      WHEN @iMonth = 5 THEN 'Mei'
      WHEN @iMonth = 6 THEN 'Juni'
      WHEN @iMonth = 7 THEN 'Juli'
      WHEN @iMonth = 8 THEN 'Agustus'
      WHEN @iMonth = 9 THEN 'September'
      WHEN @iMonth = 10 THEN 'Oktober'
      WHEN @iMonth = 11 THEN 'November'
      WHEN @iMonth = 12 THEN 'Desember'
      ELSE 'Undefined' END
RETURN (@MonthName)
END

And here's how to use the function:

SELECT [dbo].[udfGetMonthName](MONTH(GETDATE())) [Month Name Now]

SELECT [dbo].[udfGetMonthName](2) [Name of 2nd Month]

SELECT [dbo].[udfGetMonthName](11) [Name of 11th Month]