how to convert UTC dateTime to local datetime

I have problem with my search range function because we store UTC dateTime in database.for example user store record and it's after midnight in UTC time zone record stamped ex "2015-06-23 01:05:34". my search range function will get wrong list of result because of UTC. I tried to convert datetime to local time in linq using .ToLocalTime() using I have this error

"LINQ to Entities does not recognize the method 'System.DateTime ToLocalTime()' method, and this method cannot be translated into a store expression."

just I want to ask how to convert utc datetime to local datetime in EF?

  var rrnForms = (from rrn in db.RrnForms
                            where
                                (DbFunctions.TruncateTime(rrn.RrnVisitPhoneDate) >= fromDateSearch.Date &&
                                 DbFunctions.TruncateTime(rrn.RrnVisitPhoneDate) <= toDateSearch.Date)
                            select rrn).ToList();
Jon Skeet
people
quotationmark

I would strongly urge you to perform the conversion the other way round: in your .NET code, perform the conversion (once) from local time to UTC, making sure you're really applying the right time zone. Then you can pass the UTC values to the LINQ query.

Aside from anything else, that means you're applying a conversion to one pair of values instead of to every single row in your database.

You'll need to consider situations where the local time is either invalid or ambiguous due to DST transitions, of course - but that's effectively an issue anyway, just hidden by converting the unambiguous way. Indeed, I'd say that you end up with much simpler-to-understand results that way. Imagine your time zone is Europe/London, which changes from BST (UTC+1) to GMT (UTC+0) at 2am local time (1am UTC) on October 25th 2015. Suppose you have records of:

ID     Date/Time UTC          Date/Time Local (would be computed)
A      2015-10-24T23:45Z      2015-10-25T00:45 (BST)
B      2015-10-25T00:15Z      2015-10-25T01:15 (BST)
C      2015-10-25T00:45Z      2015-10-25T01:45 (BST)
D      2015-10-25T01:15Z      2015-10-25T01:15 (GMT)
E      2015-10-25T01:45Z      2015-10-25T01:45 (GMT)
F      2015-10-25T02:15Z      2015-10-25T02:15 (GMT)

Note that they're in strictly ascending UTC order. Now suppose you have a query of "local date/time >= 2015-01-25T01:20". If you perform a UTC to local conversion, that will pick records C, E and F - not D, because that's 01:15 local time. I suspect you don't want that. I suspect you actually want either C, D, E and F (if you convert 2015-01-25T01:20 to the earlier occurrence) or just E and F (if you convert 2015-01-25T01:20 to the later occurrence).

people

See more on this question at Stackoverflow