In SQL Server, you can use the ALTER LOGIN statement to change the default language for a specific login.

Doing this will ensure that whenever that login connects to SQL Server, all date/time formats and system messages are presented in the correct format and language.

Example

Here’s an example to demonstrate.

ALTER LOGIN Bart
  WITH DEFAULT_LANGUAGE = German;

This changes the language for the Bart login to German.

Actually, German is just the alias. The actual name of the language is Deutsch. So you can use the name or the alias with this statement.

Now when Bart logs in and runs the following statement, he gets Deutsch as his language.

SELECT @@LANGUAGE;

Result:

Deutsch

Date Formats

Changing the login’s default language also changes the way date/time values are formatted.

For example, here’s what happens when Bart runs the following query.

SELECT FORMAT(GETDATE(), 'd');

Result:

30.03.2020

This function output the date in a format consistent with the Deutsch/German language.

However, Bart does have the ability to override these settings.

Override the Default Language

Note that Bart can still override his default language from within his session by using SET LANGUAGE.

SET LANGUAGE Spanish;

Result:

Se cambió la configuración de idioma a Español. 

He can also explicitly set a language for some queries, such as when using the FORMAT() function or the PARSE() function to format date/time values. These functions accept an optional “culture” argument that allows you to specify the language that should be used to format the output.

Here’s the same query from the previous example, but this time, Bart adds a third argument, which specifies a different language to use for the output.

SELECT 
  FORMAT(GETDATE(), 'd') AS [Default],
  FORMAT(GETDATE(), 'd', 'en-US') AS [en-US];

Result:

+------------+-----------+
 | Default    | en-US     |
 |------------+-----------|
 | 30.03.2020 | 3/30/2020 |
 +------------+-----------+