In SQL Server, you can set the language to be used for the current session. The current language setting determines the language used on all system messages, as well as the date/time formats to use.

This article demonstrates how to use T-SQL to set the current language environment in SQL Server.

Syntax

The syntax for setting the current language goes like this:

SET LANGUAGE { [ N ] 'language' | @language_var }

Where [N]'language' | @language_var is the name of the language as stored in the sys.syslanguages system compatibility view. This argument can be either Unicode or DBCS converted to Unicode.

To specify a language in Unicode, use N‘language’. If specified as a variable, the variable must be of sysname data type (the sysname data type is used for table columns, variables, and stored procedure parameters that store object names).

Example 1 – Setting the Language

Here’s an example of setting the language for the current session to British:

SET LANGUAGE British;

Result:

Changed language setting to British.

Here’s an example of setting the language for the current session to German:

SET LANGUAGE German;

Result:

Die Spracheneinstellung wurde in Deutsch geändert.

So we can see that the output is already in the newly specified language.

Example 2 – Running a Query

Changing the language impacts on how dates are formatted. Here’s an example to show what I mean.

First, we change the language to British, then run a query to convert a string into a date data type:

SET LANGUAGE British;
SELECT CONVERT(date, '09/01/2030') AS 'Convert';

Result:

+------------+
| Convert    |
|------------|
| 2030-01-09 |
+------------+
Changed language setting to British.

In this case, the date is translated as being the 9th of January.

Then we change the language to us_English and run the same query:

SET LANGUAGE us_english;
SELECT CONVERT(date, '09/01/2030') AS 'Convert';

Result:

+------------+
| Convert    |
|------------|
| 2030-09-01 |
+------------+
Changed language setting to us_english.

So in this case, the date was translated as being the 1st of September, due to the new language settings.

Setting the Language at the Query Level

Some T-SQL functions allow you to specify a language/culture to be used at the query level. For example, PARSE() accepts an optional argument that is used to determine how the provided string is formatted. This allows you to set the language/culture to be used only within that query (and even different parts of the query) without the need to change the language settings for the current session.

Here’s an example to demonstrate what I mean:

SELECT 
    PARSE('01/06/2018' AS date USING 'en-US') AS 'en-US',
    PARSE('01/06/2018' AS date USING 'en-GB') AS 'en-GB';

Result:

+------------+------------+
| en-US      | en-GB      |
|------------+------------|
| 2018-01-06 | 2018-06-01 |
+------------+------------+

If you don’t provide this argument, the language of the current session is used.

Find the Current Language

You can run the following statement to return the language currently being used:

SELECT @@LANGUAGE;

For more options, see 3 Ways to Get the Language of the Current Session in SQL Server (T-SQL).

Find the Available Languages

You can run the following code to return a list of available languages:

EXEC sp_helplanguage;

This returns a list of all languages.

To narrow it down to a particular language, add the language name. Like this:

EXEC sp_helplanguage Spanish;