A case of SQL Server Syslanguage table application

Server
-----------------------------------------------------------------------------------------------

Author: Ongyan

Welcome to reprint, please retain this statement information.

Welcome to contact me, enhydra_boy@tom.com.

----------------------------------------------------------------------------------------------

Recently, in the CSDN forum, before and after answering the question of two friends, feel the need to sum up.

The problem is this.

1 SQL Server 2000 is in Chinese, want to be able to display the date format in English?

2 SQL Server 2000 is in the Chinese version, want to be able to buy display Chinese month name?

Let us analyze, the display date format must be related to language, and should be related to the current session of the language settings, and the server, the database is not Chinese and English independent.

So the first question is simple,

Just need

Set Language us_english
Select CONVERT (varchar (), GETDATE (), 107)
Set Language Simplified Chinese

The results are as follows

Changed language setting to us_english.

--------------------
Sep 01, 2003

(The number of rows affected is 1 rows)

Changed the language setting to Simplified Chinese.


But the second question, it seems, is that we can test

Set Language Simplified Chinese

Select CONVERT (varchar (), GETDATE (), 107)

Got is

09 01,2003

September is not needed.

This requires further analysis. The language that the database supports should have a corresponding set of date month abbreviations.

Master.dbo.syslanguages is to store the corresponding information.

You can view the structure of the following syslanguages

We are concerned with the shortmonths and months of these two items.
We see this one in Simplified Chinese, shortmonths,months are all 01,02,03,...。

So no matter how you set up language, the result is 09 instead of September.

Solution, everyone thought of it. The right is to modify the system table.

Use master
sp_configure ' allow update ', ' 1 '
Go
Reconfigure with override
Go
Update Dbo.syslanguages
Set shortmonths= ' January, February, March, April, May, June, July, August, September, October, November, December ',
Months= ' January, February, March, April, May, June, July, August, September, October, November, December ' where name= ' Simplified Chinese '
Go
sp_configure ' allow update ', ' 0 '
Go
Reconfigure with override
Go


Then restart the database server.

And then run
Select CONVERT (varchar (), GETDATE (), 107)
Go

--------------------
September 01, 2003


Select CONVERT (varchar), GETDATE (), 9)
Go
----------------------------------------------------------------------------------------------------
September 1 2003 11:36:25:507am


The problem has been solved.

It seems that a lot of problems need to think carefully, analysis, look at the documents, their own manual practice, can be solved.