Language Conversion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Other SQL Server 2012 Topics

Language Conversion

Author

Topic

GREEE
Starting Member

5 Posts

Posted - 2012-08-08 : 10:15:09

We have a new instance of Server 2008 and SQL Server 2012 running. There are certain date/time strings stored as character strings throughout our database. The problem we are having is that the conversion from text is failing because it is attempting to do the conversion to spanish. We dont want it in spanish. The server Locality is set to US, its keyboard is set to us, the default SQL user creation is set to English, as are all of the SQL users. . .so Im not sure where the Spanish is coming from. This installation of the server2008 and sql2012 were done in Mexico, and not by us, so I presume it was done in spanish and then changed, leaving some old vestage of the Spanish language settings, but I can not find it. The net result is our failure to convert errors where we store dates in text, and spanish dates where we store them as datetimes. Any thoughts about how to get everything to agree in English?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 10:26:46

is collation set to spanish for column?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GREEE
Starting Member

5 Posts

Posted - 2012-08-08 : 10:34:40

Good Question, where do I find that setting, is it accessible through SSMS or is it a windows setting?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-08-08 : 10:35:44

don't store them as string. Always store dates as dates.

Is it too late to change this?

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 10:38:55

quote:
Originally posted by GREEE

Good Question, where do I find that setting, is it accessible through SSMS or is it a windows setting?

just use

sp_help 'tablename'

or use

SELECT COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'your column name' AND TABLE_NAME ='your tablename'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GREEE
Starting Member

5 Posts

Posted - 2012-08-08 : 10:42:21

I just found it under General Settings prior to your post. . .that is it, it is set to Latin, is it a trivial thing to change I wonder. . .regarding the other post, I agree, we plan to change the dates stored as text, that exists in a few places, but the problem remains that we dont want the dates in Spanish. Im going to poke around at how to change this, but if you have any suggestions please make them. Your help is appreciated thus far, I feel like youve got me on the right path!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 10:51:52

quote:
Originally posted by GREEE

I just found it under General Settings prior to your post. . .that is it, it is set to Latin, is it a trivial thing to change I wonder. . .regarding the other post, I agree, we plan to change the dates stored as text, that exists in a few places, but the problem remains that we dont want the dates in Spanish. Im going to poke around at how to change this, but if you have any suggestions please make them. Your help is appreciated thus far, I feel like youve got me on the right path!

you can change it using ALTER TABLE <tablename> ALTER COLUMN <columnname> datatype... COLLATE <new collation>

but the existing data format will not change and hence you might have to change it manually if you need

Also if its only date values you're storing then i would make datatype as date or datetime so that we dont have to worry about collation issues at all as dates are always stored as numeric value internally

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GREEE
Starting Member

5 Posts

Posted - 2012-08-08 : 11:35:15

I found an interesting post on changing the collation for those that are trying to do this:

http://blogs.msdn.com/b/psssql/archive/2008/08/29/how-to-rebuild-system-databases-in-sql-server-2008.aspx

However, upon further reflection, Im not sure that is the problem.

I have four other servers (state side) that are configured with the same Collation setting. . .i was looking for a collation setting to switch to that would be somthing like "US" or "English" and I was unable to find any in the list. So I checked my state side servers which have english datetimes not spanish datetimes. In both cases, the collation setting is SQL_Latin1_General_CP1_CI_AS . . .The latin was what caught my attention but i guess that is referring to latin based languages generic. . .not latin american as I initially suspected.

So I am back to my initial problem. Disregard the improperly stored values that are stored as text. I agree i need to change those. . .but my datetimes that are stored as datetimes, when they are converted to text (as in placing the date text in an email alert), they are appearing in Spanish.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-08-08 : 11:40:45

if it's just a display layer problem then use an explicit convert

BEGIN TRAN

DECLARE @foo DATE = '20130101'

SELECT CONVERT(vARCHAR(20), @foo, 101)
SELECT CONVERT(vARCHAR(20), @foo, 102)
SELECT CONVERT(vARCHAR(20), @foo, 103)
SELECT CONVERT(vARCHAR(20), @foo, 104)
SELECT CONVERT(vARCHAR(20), @foo, 105)
SELECT CONVERT(vARCHAR(20), @foo, 106)
SELECT CONVERT(vARCHAR(20), @foo, 107)

-- etc....
ROLLBACK

Check out CONVERT in books online for more information

Transact Charlie

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

http://nosqlsolution.blogspot.co.uk/

GREEE
Starting Member

5 Posts

Posted - 2012-08-08 : 12:58:03

I appreciate all of your input and help. . .I learned a few new things from both of you. . .the answer to this problem however is NOT related to SQL, but to the VS code outputting to SQL and the server settings. It seems that regardless of what your User settings and Locale are, its the default that the runtime environment is paying attention to. Changing the default setting from Mexico to english in the resgistry would be a nightmare waiting to happen, fortunately there is an easy way to do it. . .buried deeply and obscurely by Microsoft. See this post:

http://windows.microsoft.com/en-US/windows-vista/Apply-regional-and-language-settings-to-reserved-accounts
Changing this default value to the users value (in my case English) fixed the issue.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources