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?

If you treat the datetime data *as* datetime data throughout, there shouldn't be an issue. Formatting issues should only arise if you convert the data to *strings*. *Avoid* the conversions as much as possible. So, how are you ending up with *strings* like `12/16/2015 09:06:15` in the first place? — Damien_The_Unbeliever, Dec 18 '15 at 10:07
@Damien_The_Unbeliever Hi. I have figured this one out eventually. :) I am not using strings, I was just viewing the values of `DateTime`s in watches. And that was actually the problem... Looks like watches use the culture of the current thread. Turns out the date was correct (this : `12/16/2015 09:06:15` could not be a valid date anyway... Never too late to realize that... :D). The bug was later-on in the code. I will post the answer below. — user2173353, Dec 18 '15 at 10:35

score 1 · Answer 1 · answered Dec 18 '15 at 08:37

1

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);

answered Dec 18 '15 at 08:37

veljkoz

8,384
8
55
91

I have the impression that `ctx.Database.SqlQuery` is by design preventing SQL injection and it is not the same as `String.Format`. I think MS would be very naive putting something so risky into EF. Are you sure there is really a problem with my approach? – user2173353 Dec 18 '15 at 08:48
E.g., see here: http://stackoverflow.com/a/17861166/2173353 (I wish I could find an example in MSDN about this...). – user2173353 Dec 18 '15 at 09:23
I don't have time to test it out right now, personally I do whatever I can to the arguments before passing them to the ORM just in case.... in any case, regardless of the security issue or not, the parsing of date and passing it as argument should solve your problem as provided in this answer... – veljkoz Dec 18 '15 at 10:34

Yaugen Vlasau · Answer 2 · 2015-12-18T11:45:36.117

1

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.

edited Dec 18 '15 at 11:45

answered Dec 18 '15 at 08:38

Yaugen Vlasau

2,148
1
17
38

Thanks, but I can't change the DB. :) – user2173353 Dec 18 '15 at 09:49

user2173353 · Accepted Answer · 2015-12-18T12:22:34.753

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.

Wrong DateTime parsing when using other cultures

3 Answers3