MS SQL setting language Globally- implications?

Question

I had an issue to do with converting dates from strings on my database (Ms SQl Server 2008) and i found a solution using

EXEC sp_defaultlanguage 'username', 'british'

what are the dangers of changing the language from US English to British English globally on the current data and the new data?

I just added some examples to my answer, please have a look there... — Shnugo, Apr 05 '16 at 15:31

score 2 · Answer 1 · answered Apr 05 '16 at 09:13

From here:

Each SQL Server has a default language. You can see what the default language is by executing these commands (in Query Analyzer).
sp_configure ‘default language’
This will tell you what the default language is (sort of). It actually returns a config_value with an integer that represents the language id. You can then run…
sp_helplanguage
You will see a list of languages that SQL Server supports. The odd thing here is that the server’s language setting will not solve your problem. This setting configures the default language for NEW users. By changing this setting, existing users will continue to have their original language. This is where it gets interesting because it’s the login’s language setting that determines SQL Server’s date format. For example, if user A has a default language of us_english, then a date of 4/6/2006 will be interpreted as April 4, 2006. If user B has a default language of ‘British’, then the date will be interpreted as June 6, 2006. The good news is that you can change the default language for a user so that subsequent logins will exhibit the correct interpretation of dates. Here’s how: You can set the default language for a user by issueing the following command.
sp_defaultlanguage @loginame = ‘LoginName’, @language = ‘Language’
After running this command, you will need to logout and back in to the database in order for the change to take affect. The good news is that the language setting only needs to be done once (for each user in the database).

Great, did not know about the default language being connected with the login. Upvote from my side! — Shnugo, Apr 05 '16 at 09:15
that s the command I used in the question, so what are implications of setting the language globally on the user — user6001539, Apr 05 '16 at 09:40

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

Have a look here

And try this

select * from sys.syslanguages

The language setting is very much involved in automatic formats. Especially Date-values and the calculation of weekdays can lead to differences.

The most problems you'll get with the conversion of date-literals (as you have encountered already).

I would NOT do this. I'd prefer to use proper formats. Read this

UPDATE

Look at these examples

SET LANGUAGE ENGLISH;
DECLARE @d1 DECIMAL(10,2)=100.50;
SELECT FORMAT(@d1,'C')
      ,@@DATEFIRST
      ,DATEPART(WEEK,{d'2016-04-03'})
      ,FORMAT({d'2016-04-03'},'D'); 
--$100.50 | 7 | 15 |    Sunday, April 3, 2016

SET LANGUAGE GERMAN;
DECLARE @d2 DECIMAL(10,2)=100.50;
SELECT FORMAT(@d2,'C')
      ,@@DATEFIRST
      ,DATEPART(WEEK,{d'2016-04-03'})
      ,FORMAT({d'2016-04-03'},'D'); 
--100,50 € |    1 | 14 |    Sonntag, 3. April 2016

If you have reports depending on the week's number you would get different results.
Any output could change...
You might run into new exceptions when you have some code (interfaces!) where you deal with external data.
Especially with the information given by Rahul Tripathi all this could even differ from user to user...

So once again: Don't

MS SQL setting language Globally- implications?

2 Answers2

UPDATE

Linked