SET LANGUAGE (TSQL Statement)

Change the current LANGUAGE.

Syntax

SET LANGUAGE language

Arguments

language: is a supported language or an alias. It is an identifier, a literal varchar or a variable of type varchar.

Remarks

The current language contains information like name of days of the week, name of months, default date formats, decimal point symbol, etc.

This is an excerpt from the RSQL internal code specifying these information for the language fr-CH:

    French (Switzerland)
    --------------------
    Locale Name:              "fr-CH"
    Locale Description:       "French (Switzerland)"
    Name of Days:             ["dimanche", "lundi", "mardi", "mercredi", "jeudi", "vendredi", "samedi"]
    Name of Days Abbr:        ["dim.", "lun.", "mar.", "mer.", "jeu.", "ven.", "sam."]
    Name of Months:           ["janvier", "février", "mars", "avril", "mai", "juin", "juillet", "août", "septembre", "octobre", "novembre", "décembre"]
    Name of Months Abbr       ["janv.", "févr.", "mars", "avr.", "mai", "juin", "juil.", "août", "sept.", "oct.", "nov.", "déc."]
    AM Symbol:                ""
    PM Symbol:                ""
    First Day of Week:        1
    Date Separator:           '.'
    Time Separator:           ':'
    Datetime Pattern Full:    "dddd d MMMM yyyy HH:mm:ss"
    Date Pattern Long:        "dddd d MMMM yyyy"
    Date Pattern Short:       "dd.MM.yyyy"
    Time Pattern Long:        "HH:mm:ss"
    Time Pattern Short:       "HH:mm"
    DMY:                      "DMY"
    Number Decimal Point:     '.'
    Number Group Separator:   '\''
    Number Grouping:          [3]

The current language affects functions like DATEPART, DATENAME, FORMAT, and CAST and CONVERT from varchar to datetime and the other way around.

Underscores in language are replaced by hyphens. E.g. fr_FR is the same as fr-FR.

The statements SET DATEFIRST and SET DATEFORMAT override the corresponding default settings defined by the current language.

The function @@LANGUAGE returns the current language.
The function @@DATEFIRST returns the current DATEFIRST setting.
The function @@DATEFORMAT returns the current DATEFORMAT setting

With RSQL, the statement SET LANGUAGE resets the SET DATEFIRST and SET DATEFORMAT settings to the language defaults. With MS SQL Server, this is not the case.

Also, MS SQL Server doesn’t implement the function @@DATEFORMAT.

The command SHOW LANGUAGES prints a list of all available languages.

Examples

DECLARE @d DATETIME = '19870302';
DECLARE @n NUMERIC(12, 2) = 1234567.89;
DECLARE @s VARCHAR(20) = 'French';

-- all the following statements sets the current language to fr-FR

SET LANGUAGE fr_FR;       -- fr_FR is the same as fr-FR
SET LANGUAGE [fr_FR];
SET LANGUAGE 'fr_FR';
SET LANGUAGE [fr-FR];
SET LANGUAGE 'fr-FR';
SET LANGUAGE @s;          -- a variable can be used

-- French and Français are aliases for fr-FR
SET LANGUAGE French;
SET LANGUAGE [French];
SET LANGUAGE 'French';

PRINT '=== The current language changed to ' + @@LANGUAGE + '===';

PRINT DATENAME(dw, @d);
PRINT 'DATEFIRST  of current language is: ' + cast(@@DATEFIRST AS VARCHAR);
PRINT 'DATEFORMAT of current language is: ' + @@DATEFORMAT;
PRINT FORMAT(@d, 'f');
PRINT FORMAT(@n, '#,##0.00');

SET LANGUAGE english;

PRINT '';
PRINT '=== The current language changed to ' + @@LANGUAGE + '===';

PRINT DATENAME(dw, @d);
PRINT 'DATEFIRST  of current language is: ' + cast(@@DATEFIRST AS VARCHAR);
PRINT 'DATEFORMAT of current language is: ' + @@DATEFORMAT;
PRINT FORMAT(@d, 'f');
PRINT FORMAT(@n, '#,##0.00');

PRINT '';
PRINT '** Change DATEFIRST and DATEFORMAT **';
SET DATEFIRST 1;
SET DATEFORMAT DMY;

PRINT '';
PRINT '=== The current language is ' + @@LANGUAGE + '===';

PRINT DATENAME(dw, @d);
PRINT 'DATEFIRST  of current language is: ' + cast(@@DATEFIRST AS VARCHAR);
PRINT 'DATEFORMAT of current language is: ' + @@DATEFORMAT;
PRINT FORMAT(@d, 'f');
PRINT FORMAT(@n, '#,##0.00');

SET LANGUAGE english;

PRINT '';
PRINT '=== The current language changed to ' + @@LANGUAGE + '===';

PRINT DATENAME(dw, @d);
PRINT 'DATEFIRST  of current language is: ' + cast(@@DATEFIRST AS VARCHAR);
PRINT 'DATEFORMAT of current language is: ' + @@DATEFORMAT;
PRINT FORMAT(@d, 'f');
PRINT FORMAT(@n, '#,##0.00');

The result is:

=== The current language changed to fr-FR===
lundi
DATEFIRST  of current language is: 1
DATEFORMAT of current language is: DMY
lundi 2 mars 1987 00:00
1 234 567,89

=== The current language changed to en-US===
Monday
DATEFIRST  of current language is: 7
DATEFORMAT of current language is: MDY
Monday, March 02, 1987 12:00 AM
1,234,567.89

** Change DATEFIRST and DATEFORMAT **

=== The current language is en-US===
Monday
DATEFIRST  of current language is: 1   <--- default DATEFIRST  setting for english is overriden
DATEFORMAT of current language is: DMY <--- default DATEFORMAT setting for english is overriden
Monday, March 02, 1987 12:00 AM
1,234,567.89

=== The current language changed to en-US===
Monday
DATEFIRST  of current language is: 7   <--- default DATEFIRST  setting for english
DATEFORMAT of current language is: MDY <--- default DATEFORMAT setting for english
Monday, March 02, 1987 12:00 AM
1,234,567.89

Guide