Get Language specific Month Name from SQL

Since you cannot use data modified scripts inside funcations in SQL server, you cannot use SET LANGUAGE inside a fucntion. Here is the way to get the month name in particular language in SQL server. You can use this function anywhere you want, anywhere you want to call.

Create a scalar valued function with any name, here I am using “fn_GetMonthName”.

Call this function with two parameters passd : “select dbo.fn_GetMonthName ( ‘2012-12-12’, ‘French’)” i.e. first parameter is the datetime value and secod parameter is the language that you want the month in from the date mentioned here.

For example, to get the name of current date in Spanish, you can use :

select dbo.fn_GetMonthName ( GetDate(), ‘Spanish)”

output : Febrero


CREATE FUNCTION [dbo].[fn_GetMonthName]
( @Date DateTime, @Language NvarChar(100))
RETURNS nvarchar(400)

declare @i int, @m int,@mlist NVARCHAR(1000)

set @m = month(@Date)
set @mlist = (SELECT months FROM sys.syslanguages where alias = @language)
set @i = 1

while(@i < @m)
           set @mlist = replace(@mlist, substring(@mlist,1,charindex(‘,’,@mlist)) ,”)
           set @i = @i + 1

set @mlist = (case charindex(‘,’,@mlist) when 0 then @mlist else substring(@mlist,0,charindex(‘,’,@mlist) ) end )
return @mlist