When working with dates in SQL Server, it’s easy to get tripped up with different date formats. For example, someone from the US might consider 01/07/2018 to mean the 7th of January, but someone from the UK might consider it to mean the 1st of July.

In many cases you might not even know which date format is used for any particular language/culture. Fortunately, SQL Server stores this information in its resource database, and you can retrieve it by using either of the two methods below.

Method 1: The sp_helplanguage Stored Procedure

The sp_helplanguage stored procedure returns information about a particular alternative language or about all languages in SQL Server. This includes the language name, its alias, and date format, and month names, associated with the respective language.

To return info for all languages in SQL Server, run the following:

EXEC sp_helplanguage;

This returns quite a large result set. Here’s the full list of languages I get when I run that on my instance of SQL Server 2017.

If you don’t want all languages returned, you can narrow it down to a specific language.

Here’s an example:

EXEC sp_helplanguage Spanish;

Here’s the result when I run it in mssql-cli:

-[ RECORD 1 ]-------------------------
langid      | 5
dateformat  | dmy
datefirst   | 1
upgrade     | 0
name        | Español
alias       | Spanish
months      | Enero,Febrero,Marzo,Abril,Mayo,Junio,Julio,Agosto,Septiembre,Octub
shortmonths | Ene,Feb,Mar,Abr,May,Jun,Jul,Ago,Sep,Oct,Nov,Dic
days        | Lunes,Martes,Miércoles,Jueves,Viernes,Sábado,Domingo
lcid        | 3082
msglangid   | 3082

You can also use @@LANGUAGE to narrow it down to whatever your current language is. Example:

EXEC sp_helplanguage @@LANGUAGE;

Result:

-[ RECORD 1 ]-------------------------
langid      | 0
dateformat  | mdy
datefirst   | 7
upgrade     | 0
name        | us_english
alias       | English
months      | January,February,March,April,May,June,July,August,September,Octobe
shortmonths | Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec
days        | Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday
lcid        | 1033
msglangid   | 1033

Method 2: The sys.syslanguages View

The second way you can return the language info is by going straight to the sys.syslanguages system compatibility view. This is the view that the above stored procedure gets its info from.

Here’s how to query this view:

SELECT * FROM sys.syslanguages;

You can also narrow it down to a language by adding a WHERE clause:

SELECT * FROM sys.syslanguages
WHERE name = 'Español';

Note that each language entry has a name and an alias. The above query could be rewritten to use the alias:

SELECT * FROM sys.syslanguages
WHERE alias = 'Spanish';

As you can probably tell, it’s much easier to use the stored procedure (1st option), but this view could be handy if you only want a subset of columns returned.

For example:

SELECT 
    name,
    alias,
    dateformat 
FROM sys.syslanguages
WHERE alias = 'Spanish';

Result:

+---------+---------+--------------+
| name    | alias   | dateformat   |
|---------+---------+--------------|
| Español | Spanish | dmy          |
+---------+---------+--------------+