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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
/* Sample use of function */ -- Current language SELECT @@LANGUAGE AS 'Current Setting'; GO -- Declare variables DECLARE @DTE1 DATETIME = '1/9/2014'; DEClARE @CNT1 INT = 0; DECLARE @LANG_TABLE TABLE ( Lang_Id INT PRIMARY KEY CLUSTERED, Lang_Russian NVARCHAR(64), Lang_English NVARCHAR(64) ); -- End of month WHILE (@CNT1 < 12) BEGIN -- Change language SET LANGUAGE Russian; -- Insert data INSERT INTO @LANG_TABLE SELECT @CNT1 + 1, DATENAME(MM, @DTE1) AS 'Russian Month Name', ''; -- Change language SET LANGUAGE English; UPDATE @LANG_TABLE SET Lang_English = DATENAME(MM, @DTE1) WHERE Lang_Id = @CNT1+1; -- Increment counter SELECT @CNT1 = @CNT1 + 1; -- Increment by one month SELECT @DTE1 = DATEADD(MM, 1, @DTE1); END -- Show the name of the months (russian/english) SELECT * FROM @LANG_TABLE GO |
The output from the first test above is listed below.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
output: Current Setting --------------- English Lang_Id Lang_Russian Lang_English ------ ------------ ----------- 1 Январь January 2 Февраль February 3 Март March 4 Апрель April 5 Май May 6 Июнь June 7 Июль July 8 Август August 9 Сентябрь September 10 Октябрь October 11 Ноябрь November 12 Декабрь December |
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
/* 2 - Where language info is stored? */ -- Various installed languages (you try) sp_helplanguage GO -- The system catalog (34) SELECT COUNT(*) as Total FROM sys.syslanguages GO |
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.