There are several ways to get the language currently being used in SQL Server.

The language of the current session will often be the default language for the login, but this is not necessarily always the case. A user can change the current language during the session.

Also, some of SQL Server’s built-in functions accept an argument that allows you to specify a language for that specific query.

This article shows you how to return the language currently being used.

@@LANGUAGE

The @@LANGUAGE configuration function is made specifically for returning the name of the language currently being used.

Here’s an example of usage.

SELECT @@LANGUAGE;

Result:

us_english

This is the default language for my login.

Here’s an example of changing the language during my session, then running @@LANGUAGE again.

SET LANGUAGE British;
SELECT @@LANGUAGE;

Result:

British

DBCC USEROPTIONS

The DBCC USEROPTIONS also allows you to get the language currently being used, along with a few other configuration options.

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 |
 +-------------------------+----------------+  

I ran that query immediately after the previous example and so it still returns British as the language.

Also note that the language implicitly affects the dateformat and datefirst values. The default values for those values when using us_english is mdy and 7 respectively.

If I change the language back to us_English and run DBCC USEROPTIONS again, you’ll see that the dateformat and datefirst settings revert to US format.

SET LANGUAGE us_English; 
 ....... DBCC USEROPTIONS;                                                       
 Time: 0.740s
 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 |
 +-------------------------+----------------+ 

However, you can always set the date format separately to the language if you need to.

sys.dm_exec_requests

The sys.dm_exec_requests view enables you to retrieve the language for a specific user process. In this case we can use @@SPID to specify the current user process.

SELECT r.language
FROM master.sys.dm_exec_requests r
WHERE r.session_id = @@SPID;

Result:

us_english