CODEWITHSUNDEEP
c#asp.netsqldatetime-format
Murtaza
Murtaza

Reputation: 3065

DateTime issue when global culture of the server is different on different servers

My website is hosted on multiple servers at different locations

Everywhere the Culture of the data format is different- we use mm/dd/yyyy format every where but incase some server has the culture set to dd/mm/yyyy then our website generates Datetime exception.

Upvotes: 8

Views: 14183

Answers (4)

Ian Boyd
Ian Boyd

Reputation: 256641

You should be specifying what culture you want to use whenever you convert a string to a date.

The culture you should be using depends on what culture the dates are formatted as. For example, if all dates you are parsing are formatted as Slovak:

String s = "24. 10. 2011";

Then you need to parse the string as though it were in Slovak (Slovakia) (sk-SK) culture:

//Bad:
d = DateTime.Parse(s);

//Good:
d = DateTime.Parse(s, CultureInfo.CreateSpecificCulture("sk-SK")); //Slovak (Slovakia)

If your dates are all in Tajik (Tajikistan Cyrillic), then you need to parse it as tg-Cryl-Tj:

String s = "24.10.11"

DateTime d = DateTime.Parse(s, CultureInfo.CreateSpecificCulture("tg-Cryl-Tj"));

Which leads to the question: what date format are you using? You should not be relying on the locale setting of the server, you should be deciding what format you want.

//Bad
String s = d.ToString();

//Good
String s = d.ToString(CultureInfo.CreateSpecificCulture("si-LK")); //Sinhala (Sri Lanka)

//s = "2011-10-24 12:00:00 පෙ.ව."

i suspect that you prefer to do everything in English. But then you have to decide which variant of English:

  • en-AU (English Austrailia): 24/10/2011
  • en-IA (English India): 24-10-2011
  • en-ZA (English South Africa): 2011/10/24
  • en-US (English United States): 10/24/2011

i suspect you prefer English (India) (en-IA).

But if you really can't decide what culture to use when converting dates to strings and vice-versa, and the dates are never meant to be shown to a user, then you can use the Invariant Culture:

String s = "10/24/2011" //invariant culture formatted date

d = DateTime.Parse(s, CultureInfo.InvariantCulture); //parse invariant culture date

s = d.ToString(CultureInfo.InvariantCulture); //convert to invariant culture string

Upvotes: 12

michael667
michael667

Reputation: 3260

Never rely on the server's default locale. For your case, this means:

  • Use prepared statements where you pass the date as (unformatted) date object and not as (formatted) string object. You should never use strings to represent dates in your application anyway, as you cannot perform date-specific functions on them (like adding 1 month, getting the last day of the current week, etc.)

  • Use SQL functions like to_date and to_char everywhere (exact names depend on your DBMS), if you really need to use string objects in your application

Upvotes: 0

Jeremy McGee
Jeremy McGee

Reputation: 25200

If deployed to a server that's not under your control it's vitally important to make sure your code doesn't have hard-coded dependencies on the culture.

You'll most likely want to search your code for DateTime.Parse or similar. We have a set of extension methods on DateTime that we use instead to force the correct culture.

Upvotes: 0

Anders Marzi Tornblad
Anders Marzi Tornblad

Reputation: 19305

Never, ever, store dates internally as strings. Not in the database, not in your app.

If you need to move date values between servers, go binary. Or if you really really have to use strings, use ToString(CultureInfo.InvariantCulture) - or simply serialize the Ticks property.

Also, never pass dates as strings to the database using SQL commands that you build using code. Use SqlParameter for that, or even better, rely on some O/R Mapper, such as Entity Framework or Linq to SQL.

Upvotes: 1

Related Questions