use set language in a sql function

Question

I want to use DATENAME in a function, but the problem is datename's result is dependent on login or install language setup.

to make sure all the time it return english result I need to use set language 'English' but if I use it inside a function I will get following error

Invalid use of a side-effecting operator 'SET COMMAND' within a function.

Answer 1

Can you try this?

select  
  case datepart(dw,getdate())
  when  isnull(nullif((7- @@datefirst + 1) % 7,0),7)  then 'Sunday'
  when  isnull(nullif((7- @@datefirst + 2) % 7,0),7) then 'Monday'
  when  isnull(nullif((7- @@datefirst + 3) % 7,0),7) then 'Tuesday'
  when  isnull(nullif((7- @@datefirst + 4) % 7,0),7) then 'Wednesday'
  when  isnull(nullif((7- @@datefirst + 5) % 7,0),7) then 'Thursday'
  when  isnull(nullif((7- @@datefirst + 6) % 7,0),7) then 'Friday'
  when  isnull(nullif((7- @@datefirst + 7) % 7,0),7) then 'Saturday'
end;

Answer 2

For anyone using SQL Server 2012 or newer, this is very easy to do via the FORMAT function, as shown here:

SET LANGUAGE 'English';

DECLARE @TestDate DATETIME;
SET @TestDate = '2015-06-23';

SELECT FORMAT(@TestDate, 'dddd'); -- Tuesday
SELECT FORMAT(@TestDate, 'dddd', 'en-us'); -- Tuesday
SELECT FORMAT(@TestDate, 'dddd', 'ru-RU'); -- вторник
SELECT FORMAT(@TestDate, 'dddd', 'fa-IR'); -- سه شنبه

SET LANGUAGE 'French';

SELECT FORMAT(@TestDate, 'dddd'); -- mardi
SELECT FORMAT(@TestDate, 'dddd', 'en-us'); -- Tuesday
SELECT FORMAT(@TestDate, 'dddd', 'ru-RU'); -- вторник
SELECT FORMAT(@TestDate, 'dddd', 'fa-IR'); -- سه شنبه

But what about anyone still using SQL Server 2005, 2008, or 2008 R2? Well, the same can be accomplished using SQLCLR. And in fact, the FORMAT function that was introduced in SQL Server 2012 (i.e. the one shown above), is using SQLCLR behind the scenes. So you can either write a simple CLR UDF to do this, or you can simply download the SQL# library (which I am the author of, but this function is available in the Free version) and use the Date_Format function, as shown here:

SET LANGUAGE 'English';

DECLARE @TestDate DATETIME;
SET @TestDate = '2015-06-23';

SELECT SQL#.Date_Format(@TestDate, 'dddd', ''); -- Tuesday
SELECT SQL#.Date_Format(@TestDate, 'dddd', 'en-us'); -- Tuesday
SELECT SQL#.Date_Format(@TestDate, 'dddd', 'ru-RU'); -- вторник
SELECT SQL#.Date_Format(@TestDate, 'dddd', 'fa-IR'); -- سه شنبه

SET LANGUAGE 'French';

SELECT SQL#.Date_Format(@TestDate, 'dddd', ''); -- Tuesday (Windows lang, not SQL Server's)
SELECT SQL#.Date_Format(@TestDate, 'dddd', 'en-us'); -- Tuesday
SELECT SQL#.Date_Format(@TestDate, 'dddd', 'ru-RU'); -- вторник
SELECT SQL#.Date_Format(@TestDate, 'dddd', 'fa-IR'); -- سه شنبه

The SQL# Date_Format function has essentially the same signature as the built-in FORMAT function, except that the 3rd parameter (Culture) cannot be omitted in a SQLCLR function, so when not using a culture it would need to be set to an empty string ''. Another difference is that SQLCLR does not seem to be aware of the current session LANGUAGE setting, so the default culture is whatever Windows is set to (but that is specifically a non-issue in this particular case).

P.S. See the MSDN page for Custom Date and Time Format Strings for more formatting options.