Login Default Languages and the Effects of Getting it Wrong

There have been countless times where I’ve seen dates in SQL Server being interpreted differently to what people have expected. Sometimes the date is interpreted in the format dd-mm-yyyy whereas other times the date format is interpreted in the mm-dd-yyyy format. This can greatly change the results of queries or change the date that was intended to be inserted/updated.

This comes down to the default language setting of the login you are logged in as, and also the default language the service account your application is set to connect to the database with.

Let me demonstrate. Firstly, I’ll create two SQL Server logins, one with the default language set to us_english which we will call Donald, and the other with the default language set to British which we will call Boris:

CREATE LOGIN [Donald] WITH PASSWORD=N'USA4Life', DEFAULT_LANGUAGE=[us_english]
CREATE LOGIN [Boris] WITH PASSWORD=N'UKRules!', DEFAULT_LANGUAGE=[British]

I’ll run a few queries to check the output of what is returned when our different logins are connected. First up Donald logs in, which is using the default language us_english, and runs some simple date related queries:

It shows that when logged in with the login Donald, it interprets the date 05/10/2020 as 10th May 2020. What happens when Boris, which has the default language set to British, runs the same query:

We can see Boris gets different results from the same query. Boris interprets the date as 5th October 2020. Quite different to what Donald sees. It’s even worse (or is it better?) when you use a date that will result in an error like 05/30/2020, which to Donald would be 30th May 2020, but to Boris that’s too crazy to figure out since there is no 30th month:

We can fix this

There are a few ways to ensure this mismatch doesn’t happen. The first way is to write your queries using a date format that’ll work no matter what the default language is set to – yyyymmdd format. You can’t go wrong. Donald and Boris both agree on this – they now output the same, 5th October 2020:

Now that we know how to write dates in our SQL Server queries, it would be a good idea to have all your logins set to the same default language, so at least everyone gets the same results. This might not be allowed in your company, but if you are allowed to, then I suggest you do it (but test it first, especially with service account logins!).

This can be done by running an alter command on an individual login. Lets do this by changing our login Donald to the default language British:

ALTER LOGIN [Donald] WITH DEFAULT_LANGUAGE=[British];

Or, you might want to change all your logins. Check what default language all your logins are set to and once you’re sure you can change them all to the same default language, then you can go ahead and change them. Definitely keep a copy of what they were set to before changing them. The script below changes them all to British:

DECLARE @sql_command NVARCHAR(4000) = N'';

SELECT @sql_command += N'ALTER LOGIN [' + [name] + N'] WITH  DEFAULT_LANGUAGE=[British];' + NCHAR(13)
FROM sys.server_principals 
WHERE [type] IN ('U', 'S') 
	AND [name] NOT LIKE N'NT %' AND [name] NOT LIKE N'##MS%' AND principal_id <> 0x01 -- not system logins
	AND default_language_name <> N'British';

EXECUTE sp_executesql @sql_command;

Now we’re all good right? All logins are the same default language, so all dates will get interpreted the same now. That’s until another login gets created by the other DBA who you forgot to mention that all new logins should be set a different default language, or more likely by that developer who has sysadmin privileges. It’s easier if you simply set the default language at the SQL Server instance level. This will mean all new logins created will be set to the default language that you want it to be:

EXECUTE sp_configure 'default language', N'23';
RECONFIGURE WITH OVERRIDE;

What’s 23 you ask? That’s the language ID of the language British. us_english is 0. You can check all the languages by running:

EXECUTE sp_helplanguage;

You can add the command to set the default language of the instance to your set of configuration changes when building a new SQL Server instance. And while we’re on that subject, a hot tip for you, when you add sysadmin members as part of the SQL Server install, they get added with default language of us_english, so you might want to change their default language too once it’s installed.