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)
AS

BEGIN
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)
begin
           set @mlist = replace(@mlist, substring(@mlist,1,charindex(‘,’,@mlist)) ,”)
           set @i = @i + 1
end

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

====================================