SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Other SQL Server 2012 Topics
 Language Conversion
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

GREEE
Starting Member

USA
5 Posts

Posted - 08/08/2012 :  10:15:09  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 08/08/2012 :  10:26:46  Show Profile  Reply with Quote
is collation set to spanish for column?

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

Go to Top of Page

GREEE
Starting Member

USA
5 Posts

Posted - 08/08/2012 :  10:34:40  Show Profile  Reply with Quote
Good Question, where do I find that setting, is it accessible through SSMS or is it a windows setting?
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 08/08/2012 :  10:35:44  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/08/2012 :  10:38:55  Show Profile  Reply with Quote
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/

Go to Top of Page

GREEE
Starting Member

USA
5 Posts

Posted - 08/08/2012 :  10:42:21  Show Profile  Reply with Quote
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!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/08/2012 :  10:51:52  Show Profile  Reply with Quote
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/

Go to Top of Page

GREEE
Starting Member

USA
5 Posts

Posted - 08/08/2012 :  11:35:15  Show Profile  Reply with Quote
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.
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 08/08/2012 :  11:40:45  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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/

Edited by - Transact Charlie on 08/08/2012 11:41:30
Go to Top of Page

GREEE
Starting Member

USA
5 Posts

Posted - 08/08/2012 :  12:58:03  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000