Wrong DateTime parsing when using other cultures

Question

I am using EF in a multi-language site to map results from SPs to objects.

ctx.Database.SqlQuery<MyDTO>("MySP {0}", Id).SingleOrDefault()

I see that there is an issue with mapping dates when the culture changes.

More specifically, I get the same date either as 16/12/2015 09:06:15 or 12/16/2015 09:06:15, depending on the user culture.

I know two solutions to this:

• Get the date as a string and parse it with CultureInfo.InvariantCulture.
• Switch the culture to CultureInfo.CultureInvariant before calling the repository methods.

And perhaps there is another option:

• I see that we change both: Thread.CurrentThread.CurrentUICulture and Thread.CurrentThread.CurrentCulture to the user's locale, but I think we should only switch the UI one. But I am not sure what will break if I change this...

Is there any alternative, like setting the culture on the EF context?

---

Update: Chaning the Thread.CurrentThread.CurrentUICulture and Thread.CurrentThread.CurrentCulture just before doing the query doesn't seem to help either. That is confusing... Perhaps EF caches the culture at an earlier point?

Answer 1

I have figured this one out eventually. :)

I was viewing the values of DateTimes in watches. And that was actually the problem... Looks like watches use the culture of the current thread (and thus switching the order of month and day from culture to culture). Turns out the date was correct (this : 12/16/2015 09:06:15 could not be a valid date anyway if you consider 16 to be the month... Never too late to realize that...).

So, VS watchers disorientated me...

Good to know that the current thread culture does not affect the parsing of SQL data, as I would expect. :)

The actual bug was later-on in the code.

Answer 2

You Can store the date time either in long (teaks) or real (the number you get when your cast datetime to real). Then you can convert these values to any culture without any loss.

Answer 3

First off, you should avoid calling your stored procedures like that - it's an easy way to get that OWASP Top 10 Sql Injection security issue.

Instead, you should call by passing arguments to the stored procedure as actual parameters, for example something like this:

string dateAsString = "12/16/2015 09:06:15";
string dateFormat = "MM/dd/yyyy HH:mm:ss";
DateTime theValue = DateTime.Parse(dateAsString, dateFormat, CultureInfo.InvariantCulture);
SqlParameter myDate = new SqlParameter("@theDate", theValue);
context.Database.ExecuteSqlCommand("MySP @theDate", theDate);