CODEWITHSUNDEEP
sql-server-2008
user6001539
user6001539

Reputation: 53

MS SQL setting language Globally- implications?

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?

Upvotes: 1

Views: 481

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

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

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172378

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

Upvotes: 2

Related Questions