CODEWITHSUNDEEP
c#.netdatetime
Ammar Khan
Ammar Khan

Reputation: 2585

Retrieve Record from Database based on Local Time

Let's suppose I live in a timezone where Today Date is: 12/3/2014 Before saving record I save the date in UTC, so it would be 12/2/2014

user.Created = DateTime.UtcNow;

Now I have a Search page on which I would like to retrieve record based on the filter "Today". Also I would like to truncate time so I am using DbFunction of EF.

 DateTime dateDifference = DateTime.Now.Date;
 var result= queryableData
       .Where(s => dateDifference ==
         DbFunctions.CreateDateTime(s.Created.Year, s.Created.Month,
             s.Created.Day, 0, 0, 0)).AsQueryable();

The above query does not return any record because the stored date is in UTC which 12/2/2014, however I am living in a timezone where date is 12/3/2014. So it make sense, how can I modify the above query to meet my requirement. Any suggestion

Upvotes: 2

Views: 572

Answers (2)

Jon Skeet
Jon Skeet

Reputation: 1503290

Now that we know that the value is stored as a DateTime (in UTC), you just need to find the UTC time of midnight at the start of today, and the UTC time of midnight at the end of today.

If you're happy to use the system time zone, you could use:

DateTime today = DateTime.Today;
DateTime startUtc = today.ToUniversalTime();
DateTime endUtc = today.AddDays(1).ToUniversalTime();

var result = queryableDate.Where(s => startUtc <= s.Created &&
                                      s.Created < endUtc);

I don't think you should need DbFunctions for that query. Basically, you don't need a difference at all - you just need to work out what your filter of "today" means in terms of an interval in time, expressed as UTC.

Upvotes: 3

Resource
Resource

Reputation: 544

Convert your local date to universal time.

DateTime universalDate = dateDifference.ToUniversalTime();

Upvotes: 0

Related Questions