You may know that when you connect to SQL Server, the language for that session is usually determined by your login. When a login is created, it is assigned a default language.

The session language determines the datetime formats and system messages.

While you can certainly change the default language for a login, you can also override the default language within a session if you need to. You can toggle back and forth between languages if required. Or you could even open two separate connections and apply a different language to each of them.

This article explains how to change the language within a session.

SET LANGUAGE

The SET LANGUAGE statement allows you to set the language environment for the current session.

Here’s an example.

SET LANGUAGE British;

That sets the current language to British.

I can verify this with the following query.

SELECT @@LANGUAGE;

Result:

British

Date Formats

You should know that changing the language within a session also changes the date format.

Here’s another query to illustrate this.

DBCC USEROPTIONS;

Result:

+-------------------------+----------------+
 | Set Option              | Value          |
 |-------------------------+----------------|
 | textsize                | -1             |
 | language                | British        |
 | dateformat              | dmy            |
 | datefirst               | 1              |
 | lock_timeout            | 5000           |
 | quoted_identifier       | SET            |
 | arithabort              | SET            |
 | ansi_null_dflt_on       | SET            |
 | ansi_warnings           | SET            |
 | ansi_padding            | SET            |
 | ansi_nulls              | SET            |
 | concat_null_yields_null | SET            |
 | isolation level         | read committed |
 +-------------------------+----------------+ 

Notice that dateformat is dmy and datefirst is 1. This is inline with the British date format.

Here’s what happens if I change the language to us_english.

SET LANGUAGE us_english; 
 ....... DBCC USEROPTIONS;                                                             
 Time: 0.738s
 Changed language setting to us_english.
 +-------------------------+----------------+
 | Set Option              | Value          |
 |-------------------------+----------------|
 | textsize                | -1             |
 | language                | us_english     |
 | dateformat              | mdy            |
 | datefirst               | 7              |
 | lock_timeout            | 5000           |
 | quoted_identifier       | SET            |
 | arithabort              | SET            |
 | ansi_null_dflt_on       | SET            |
 | ansi_warnings           | SET            |
 | ansi_padding            | SET            |
 | ansi_nulls              | SET            |
 | concat_null_yields_null | SET            |
 | isolation level         | read committed |
 +-------------------------+----------------+  

Notice that the date format is implicitly changed to adhere to US date formatting.

If you find this problematic, you can always change the date format without changing the language.

Set Language at the Query Level

Some functions accept a “culture” argument that allows you to specify a language to use for that query only. In other words, you can change the language ad-hoc within a query, without needing to change the language of your current session.

Here’s an example.

SET LANGUAGE us_english;
SELECT 
  FORMAT(GETDATE(), 'd') AS [My Default],
  FORMAT(GETDATE(), 'd', 'en-GB') AS [British],
  FORMAT(GETDATE(), 'd', 'de-DE') AS [German];

Result:

+--------------+------------+------------+
 | My Default   | British    | German     |
 |--------------+------------+------------|
 | 3/29/2020    | 29/03/2020 | 29.03.2020 |
 +--------------+------------+------------+  

Get a List of Languages

You can run the following query to get a list of languages available in SQL Server.

EXEC sp_helplanguage;

You can also narrow it down to a specific language by appending it with the language name or alias.

EXEC sp_helplanguage Italian;