{"id":6783,"date":"2013-12-10T00:00:11","date_gmt":"2013-12-10T00:00:11","guid":{"rendered":"http:\/\/craftydba.com\/?p=6783"},"modified":"2016-04-20T01:54:58","modified_gmt":"2016-04-20T01:54:58","slug":"datetime-functions-set-language","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=6783","title":{"rendered":"Date\/Time Functions – @@LANGUAGE"},"content":{"rendered":"

I can almost see the end of my writing of short articles or tidbits on Transaction SQL date\/time functions<\/a>.<\/p>\n

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?<\/p>\n

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

The examples below are sample calls to the @@LANGUAGE function and SET LANGUAGE statement. <\/p>\n

\r\n\/*\r\n    Sample use of function\r\n*\/\r\n\r\n-- Current language \r\nSELECT @@LANGUAGE AS 'Current Setting';\r\nGO\r\n\r\n-- Declare variables\r\nDECLARE @DTE1 DATETIME = '1\/9\/2014';\r\nDEClARE @CNT1 INT = 0;\r\nDECLARE @LANG_TABLE TABLE\r\n(\r\n  Lang_Id INT PRIMARY KEY CLUSTERED,\r\n  Lang_Russian NVARCHAR(64),\r\n  Lang_English NVARCHAR(64)\r\n);\r\n\r\n-- End of month\r\nWHILE (@CNT1 < 12)\r\nBEGIN\r\n\r\n  -- Change language\r\n  SET LANGUAGE Russian;\r\n \r\n  -- Insert data\r\n  INSERT INTO @LANG_TABLE \r\n    SELECT @CNT1 + 1, DATENAME(MM, @DTE1) AS 'Russian Month Name', '';\r\n\r\n  -- Change language\r\n  SET LANGUAGE English;\r\n\r\n  UPDATE @LANG_TABLE \r\n  SET Lang_English = DATENAME(MM, @DTE1) \r\n  WHERE Lang_Id = @CNT1+1;\r\n\r\n  -- Increment counter\r\n  SELECT @CNT1 = @CNT1 + 1;\r\n\r\n  -- Increment by one month\r\n  SELECT @DTE1 = DATEADD(MM, 1, @DTE1);\r\nEND\r\n\r\n-- Show the name of the months (russian\/english)\r\nSELECT * FROM @LANG_TABLE\r\nGO\r\n<\/pre>\n<\/p>\n
The output from the first test above is listed below. <\/p>\n
\r\noutput: \r\n\r\nCurrent Setting\r\n---------------\r\nEnglish\r\n\r\nLang_Id     Lang_Russian  Lang_English\r\n------      ------------  -----------\r\n1           \u042f\u043d\u0432\u0430\u0440\u044c        January\r\n2           \u0424\u0435\u0432\u0440\u0430\u043b\u044c       February\r\n3           \u041c\u0430\u0440\u0442          March\r\n4           \u0410\u043f\u0440\u0435\u043b\u044c        April\r\n5           \u041c\u0430\u0439           May\r\n6           \u0418\u044e\u043d\u044c          June\r\n7           \u0418\u044e\u043b\u044c          July\r\n8           \u0410\u0432\u0433\u0443\u0441\u0442        August\r\n9           \u0421\u0435\u043d\u0442\u044f\u0431\u0440\u044c      September\r\n10          \u041e\u043a\u0442\u044f\u0431\u0440\u044c       October\r\n11          \u041d\u043e\u044f\u0431\u0440\u044c        November\r\n12          \u0414\u0435\u043a\u0430\u0431\u0440\u044c       December\r\n\r\n<\/pre>\n<\/p>\n
The sp_helplanguage<\/a> system stored procedure basically displays the data in the system compatibility view sys.syslanguages<\/a>.  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.<\/p>\n
The code snippet below shows the language specific information by calling the system stored procedure and gets the total number of installed languages.<\/p>\n
\r\n\/*\r\n    2 - Where language info is stored?\r\n*\/\r\n\r\n-- Various installed languages (you try)\r\nsp_helplanguage\r\nGO\r\n\r\n-- The system catalog (34)\r\nSELECT COUNT(*) as Total FROM sys.syslanguages\r\nGO\r\n\r\n<\/pre>\n<\/p>\n
I am not going to display the output since it is quite large.  <\/p>\n
However, I do want to talk about the SET DATE FORMAT<\/a>.  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.  <\/p>\n
To summarize this article, the @@LANGUAGE<\/a> configuration function tells you what language is set as the default.  This value is controlled at the session level.  The SET LANGUAGE<\/a> statement can be used to override this value.  Each database sever has a particular set of languages installed.  Use the sp_helplanguage<\/a> store procedure or SELECT from the sys.syslanguages<\/a> 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<\/a> statement.<\/p>\n","protected":false},"excerpt":{"rendered":"
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…<\/p>\n","protected":false},"author":1,"featured_media":6759,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[814],"tags":[1000,31,940,15,1002,1001,1004,1003,29],"class_list":["post-6783","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-very-short-articles","tag-language","tag-database-developer","tag-date-time-functions","tag-john-f-miner-iii","tag-set-dateformat","tag-set-language","tag-sp_helplanguage","tag-sys-syslanguages","tag-tsql"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/6783","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6783"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/6783\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/media\/6759"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6783"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6783"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6783"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}