CODEWITHSUNDEEP
c#sqldatetimeasp.net-web-apiutc
gilpach
gilpach

Reputation: 1385

UTC in DB confusion when querying in linq from Web API server

I can really use some help wrapping my head around a problem I'm having querying data according to a SQL Date field.

I am storing the Date in UTC format using the following code: 

objFitCalendarDto.Day = objFitCalendarDto.Day.ToUniversalTime();

That line assigns the date to the model that is inserted into the db through Entity Framework.

Now, my query is supposed to retrieve a row based on a date. So, I should be able to get the row for today, tomorrow, yesterday, and so on.

To do this, I'm using the method to search between two dates, a start date and an end date as follows: 

DateTime dayBegin = DateTime.Today.Date.AddDays(dayOffset);
DateTime dayEnd = DateTime.Today.Date.AddDays(dayOffset + 1);

The purpose of dayOffset is to specify which day. If Offset is 0, then I am searching for Today. If dayOffset is 1, then I am searching for rows with tomorrow's date.

Now, since I stored the data originally in UTC, I am assuming that I must search for it in UTC as well. So before executing my query, I convert the dates to UTC like so: 

dayBegin = TimeZoneInfo.ConvertTimeToUtc(dayBegin);
dayEnd = TimeZoneInfo.ConvertTimeToUtc(dayEnd);

Then I execute my query like so: 

var query = (from f in Db.FitCalendars
    where f.FitProgramId == programId &&
          f.DayAsDate >= dayBegin && f.DayAsDate < dayEnd
          select f);

problem is, it doesn't work. I have a row with the date, "2016-01-26" when I look at it in SQL Manager. However, it only returns from a query on yesterday's date. Today is 2016-01-26, by the way. Clearly I'm not getting this UTC concept. Can anyone see what I'm doing wrong here? I was assuming that if I stored everything as UTC and then before querying I converted my dates for the query to UTC, that everything should work.

Upvotes: 0

Views: 717

Answers (1)

berliner
berliner

Reputation: 1955

UPDATE Let's try like this:

  1. As soon as you are storing only date part (SQL 'date' type), you need to compare also only dates.

  2. Instead of 

DateTime dayBegin = DateTime.Today.Date.AddDays(dayOffset);   
dayBegin = TimeZoneInfo.ConvertTimeToUtc(dayBegin);

let's just do 

DateTime dayBegin = DateTime.UtcNow.Date.AddDays(dayOffset);

dayBegin in that case will be date with time anyway (time is 12:00:00 AM). It means, we need to truncate it with DbFunctions. We need equality check here.

var query = (from f in Db.FitCalendars
    where f.FitProgramId == programId &&
          f.DayAsDate == DbFunctions.TruncateTime(dayBegin)
          select f);

END OF UPDATE

I believe that problem is that you comparing dates with times. In your case you need to compare only dates, as far as I understand. As a solution - use DbFunctions TruncateTime function. It can be used within linq queries - like in your code.

https://msdn.microsoft.com/en-us/library/system.data.entity.dbfunctions.truncatetime(v=vs.113).aspx

So, complete solution would be

var query = (from f in Db.FitCalendars
    where f.FitProgramId == programId &&
          DbFunctions.TruncateTime(f.DayAsDate) >= DbFunctions.TruncateTime(dayBegin) && DbFunctions.TruncateTime(f.DayAsDate) < DbFunctions.TruncateTime(dayEnd)
      select f);

Upvotes: 1

Related Questions