Does DbFunctions.TruncateTime behave differently in different server time zones?

Question

I'm not sure that my question Title is perfect - so please allow me to explain a little further.

Here's a snapshot of some test data:

Here's my code:

Function TestDb() As ActionResult
   Dim clientLocId As Integer = 23
   Dim showDate As New Date
   showDate = New Date(2015, 8, 14)
   'showDate = New Date(2015, 9, 22)
   'showDate = New Date(2015, 9, 27)

   Dim orderRecs = db.Orders.Where(Function(x) x.ClientLocationId = clientLocId AndAlso x.OrderNumber IsNot Nothing _
                    AndAlso x.DateCompletedUtc IsNot Nothing _
                    AndAlso DbFunctions.TruncateTime(x.OrderDateLoc) = showDate.Date) _
                    .OrderByDescending(Function(x) x.OrderDateUtc)

   Stop
End Function

And so here's my problem:

The rows for Order Dates 09/27/2015 and 09/22/2015 query properly with the logic above - yielding 1 row for each date requested. BUT - a query for the 08/14/2015 date yields NOTHING. I am in the -04:00 timezone now, if that matters. If I change the timezone [edit] in the row data [end edit] to -04:00 the 2 08/14/2015 rows query properly.

I have googled to try to find answers to this but have come up dry. Can someone weigh in on my problem?

---

[UPDATE]: Workaround Here's a workaround (for now) based on a suggestion from this thread by @PiotrAuguscik suggesting to convert the query first to a list:

Dim orderRecs = (db.Orders.Where(Function(x) x.ClientLocationId = clientLocId AndAlso x.OrderNumber IsNot Nothing _
                AndAlso x.DateCompletedUtc IsNot Nothing).ToList) _
                .Where(Function(x) x.OrderDateLoc.Value.Date = showDate.Date) _
                .OrderByDescending(Function(x) x.OrderDateUtc)

It's a little "crusty", but it works. I sure would like to know, however, why timezones would have anything to do with DbFunctions.TruncateTime().

---

[UPDATE #2] Proper solution code derived from answer from Matt Johnson

Dim orderRecs = db.Orders.Where(Function(x) x.ClientLocationId = clientLocId AndAlso x.OrderNumber IsNot Nothing _
                          AndAlso x.DateCompletedUtc IsNot Nothing AndAlso
                          (x.OrderDateLoc >= showDateDto AndAlso x.OrderDateLoc < showDateDto.AddDays(1))) _
                          .OrderByDescending(Function(x) x.OrderDateUtc)

---

Answer 1

A few things:

• Both your original query and your workaround are non-sargable. You should never manipulate the left side of an comparison in a WHERE clause. If you do, the database can't use any indexes and will get slower and slower the more data you have. Instead, do a range query.

• It would appear you have datetimeoffset types in your table. These represent specific moments in time, thus comparisons against two datetimeoffset values are done based on their UTC equivalents - not on their local display time. Values are indexed this way as well.

• Not everyone observes the same calendar date at the same time. You'll need to ask yourself, "who's date am I asking for?"

  • If it's the date of the person making the query, then your input values should reflect that. Instead of passing into your query a VB Date (which is a System.DateTime) in terms of local time, either pass in a UTC-based DateTime, or a DateTimeOffset. Remember, you'll need to do a range query, so you would calculate a pair of them, as a half-open interval. In other words:

    // this example uses the local time zone, but there are other ways also.
    DateTimeOffset startDto = new DateTimeOffset(showDate.Date)
    DateTimeOffset endDto = new DateTimeOffset(showDate.Date.AddDays(1))
    
    // then in the query...
    ...   x.OrderDateLoc >= startDto && x.OrderDateLoc < endDto
    

  • If you're looking to match the local date as it is recorded, then you have additional work to do in your SQL Server database.

    • First you'll need to strip away the offset by convert(datetime2, yourDateTimeOffset), or just compute the raw local date by convert(date, yourDateTimeOffset). You should do this in a computed column so you can also create an index on it.

    • Then later, you can use that computed column to do the range query, or if you computed down to the date then you can just do an equality comparison against that.

• In general, I'd avoid using DbFunctions.TruncateTime in a where clause. It gets converted to some fairly inefficient SQL that looks like this when used against a datetimeoffset field:

  convert(datetimeoffset, convert(varchar(255), yourField, 102) + ' 00:00:00 ' + Right(convert(varchar(255), yourField, 121), 6), 102)
  

  Essentially, this uses strings to re-build the datetimeoffset while retaining the offset but setting the time to midnight, which is probably not what you really want to do. You can see this yourself in SQL Profiler.

Answer 2

This is a response to Matt Johnson's response. The queries above aren't necessarily non-sargable, it depends on the index. It becomes non-sargable when you use an index as a field function parameter. :)