Sql Server: culture changed once i moved to another server. c# .NET

Question

Recently I decided to move to another server. Now my application runs on different servers with different culture (IT and EN).

Everything worked fine but now i am facing many problems with date and numbers.

The main problem seems to be SQL Server. In fact, SQL SERVER throws errors. I solved the problem with date using the method ParseExact:

   e.Command.Parameters["@Expiry"].Value = 
             DateTime.ParseExact("31/12/2099", "dd/MM/yyyy", null);

For decimal numbers i have to swap "." and "," (depending of the culture) if i want the application to work:

   Latitude.Value = Latitude.Value.Replace(".", ","); //ONLY for ITALIAN CULTURE
   e.Command.Parameters["@Latitude"].Value = Latitude.Value;

I repeat: the errors are generated by SqlServer. Is there any way to set the culture once for all?

PS: the EN server is GoDaddy Hosting.

UPDATE: The problem is pure Sql Server. In fact, checking on my servers, I found a ',' on the first one and a '.' on the second one as separators for money and decimals.I found: ALTER LOGIN 'MYDBLOGIN' WITH DEFAULT_LANGUAGE = Italian; GO

With the previous query i was able to change the culture on sql server. However, the problem is still there.

Is this code running within a CLR object? If not, I don't see how C# code can "fix" an SQL Server error. — Damien_The_Unbeliever, Aug 22 '12 at 09:48
you can set culture using global.asax or also you can set SQL SERVER Collate — Satinder singh, Aug 22 '12 at 09:49
Are you storing dates in SQL as *char, or one of the date data types? — StuartLC, Aug 22 '12 at 09:52
The problem is pure Sql Server. In fact, checking on my servers, I found a ',' on the first one and a '.' on the second one as separators for money and decimals. I found: ALTER LOGIN 'MYDBLOGIN' WITH DEFAULT_LANGUAGE = Italian; GO With the previous query i was able to change the culture on sql server. However, the problem is still there. — user1589910, Aug 22 '12 at 10:31
@user1589910: Is it multilingual website ??, If yes then you need to set datetime according to the culture. `DateTime myDate; DateTime.TryParse(yourdate, new CultureInfo("en-US"), DateTimeStyles.None, out myDate);` — Satinder singh, Aug 22 '12 at 12:11

score 0 · Answer 1 · answered Aug 22 '12 at 09:55

0

What does this return?

 select @@LANGID, @@LANGUAGE

If it says Italian,

 set language us_english

(or whatever...)

answered Aug 22 '12 at 09:55

podiluska

50,950
7
98
104

Thank you. However, I have just found the right query: ALTER LOGIN 'MYDBLOGIN' WITH DEFAULT_LANGUAGE = Italian; GO – user1589910 Aug 22 '12 at 10:11

score 0 · Answer 2 · answered Aug 22 '12 at 11:25

0

Rather than every time changing the SQL server culture why don't you use

   Convert.ToDecimal(Latitude, CultureInfo.InvariantCulture);

answered Aug 22 '12 at 11:25

praveen

12,083
1
41
49

well, that is what i am gonna do. But i have many lines of code to change. Much better if i could change just the culture of DB. – user1589910 Aug 24 '12 at 10:32

score 0 · Answer 3 · answered Aug 22 '12 at 12:33

 DateTime myDate;
 DateTime.TryParse(yourdate, new CultureInfo("en-US"), DateTimeStyles.None, out myDate);

Or

 DateTime datet1=new DateTime(2012,5,1);
 string startdate = datet1.ToString("d", DateTimeFormatInfo.InvariantInfo);

You can change your database Collate:

ALTER DATABASE test2 -- put your database name here
   COLLATE Latin1_General_CS_AS   -- replace with whatever collation you need

Your problem is not of sqlserver, you make you modifed in your code while inserting/fetching datetime according to the CultureInfo

Sql Server: culture changed once i moved to another server. c# .NET

3 Answers3