Date/Time Functions – @@LANGUAGE

I can almost see the end of my writing of short articles or tidbits on Transaction SQL date/time functions.

We already talked about functions like DATENAME() that returns the month or day of a given date/time variable as as string. How does this function know what to return when the default language is Russian?

Today, I will be talking about @@LANGUAGE, a configuration function which returns the current session value and SET LANGUAGE, a set statement which changes the value for the current session (SPID). Last but not least, the sp_helplanguage tells the developer what languages are installed on the server.

The examples below are sample calls to the @@LANGUAGE function and SET LANGUAGE statement.

The output from the first test above is listed below.

The sp_helplanguage system stored procedure basically displays the data in the system compatibility view sys.syslanguages. Things like date first, date format, month name and day name are a few of the items defined in this table. On my system, there are 34 different language settings.

The code snippet below shows the language specific information by calling the system stored procedure and gets the total number of installed languages.

I am not going to display the output since it is quite large.

However, I do want to talk about the SET DATE FORMAT. It defines the order of the month, day, and year date parts for interpreting character strings. This is automatically set when the language is changed.

To summarize this article, the @@LANGUAGE configuration function tells you what language is set as the default. This value is controlled at the session level. The SET LANGUAGE statement can be used to override this value. Each database sever has a particular set of languages installed. Use the sp_helplanguage store procedure or SELECT from the sys.syslanguages compatibility view to see what choices you have. As a side effect, the date format is change when the language is changed. You can override this setting by calling the SET DATEFORMAT statement.

Related posts

Leave a Comment