What's a good way to check if two datetimes are on the same calendar day in TSQL?

Question

This is much more concise:

where 
  datediff(day, date1, date2) = 0

You pretty much have to keep the left side of your where clause clean. So, normally, you'd do something like:

WHERE MyDateTime >= @activityDateMidnight 
      AND MyDateTime < (@activityDateMidnight + 1)

(Some folks prefer DATEADD(d, 1, @activityDateMidnight) instead - but it's the same thing).

The TimeZone table complicates matter a bit though. It's a little unclear from your snippet, but it looks like t.TheDateInTable is in GMT with a Time Zone identifier, and that you're then adding the offset to compare against @activityDateMidnight - which is in local time. I'm not sure what ds.LocalTimeZone is, though.

If that's the case, then you need to get @activityDateMidnight into GMT instead.

where
year(date1) = year(date2)
and month(date1) = month(date2)
and day(date1) = day(date2)

Make sure to read Only In A Database Can You Get 1000% + Improvement By Changing A Few Lines Of Code so that you are sure that the optimizer can utilize the index effectively when messing with dates

this will remove time component from a date for you:

select dateadd(d, datediff(d, 0, current_timestamp), 0)

Eric Z Beard:

I do store all dates in GMT. Here's the use case: something happened at 11:00 PM EST on the 1st, which is the 2nd GMT. I want to see activity for the 1st, and I am in EST so I will want to see the 11PM activity. If I just compared raw GMT datetimes, I would miss things. Each row in the report can represent an activity from a different time zone.

Right, but when you say you're interested in activity for Jan 1st 2008 EST:

SELECT @activityDateMidnight = '1/1/2008', @activityDateTZ = 'EST'

you just need to convert that to GMT (I'm ignoring the complication of querying for the day before EST goes to EDT, or vice versa):

Table: TimeZone
Fields: TimeZone, Offset
Values: EST, -4

--Multiply by -1, since we're converting EST to GMT.
--Offsets are to go from GMT to EST.
SELECT @activityGmtBegin = DATEADD(hh, Offset * -1, @activityDateMidnight)
FROM TimeZone
WHERE TimeZone = @activityDateTZ

which should give you '1/1/2008 4:00 AM'. Then, you can just search in GMT:

SELECT * FROM EventTable
WHERE 
   EventTime >= @activityGmtBegin --1/1/2008 4:00 AM
   AND EventTime < (@activityGmtBegin + 1) --1/2/2008 4:00 AM

The event in question is stored with a GMT EventTime of 1/2/2008 3:00 AM. You don't even need the TimeZone in the EventTable (for this purpose, at least).

Since EventTime is not in a function, this is a straight index scan - which should be pretty efficient. Make EventTime your clustered index, and it'll fly. ;)

Personally, I'd have the app convert the search time into GMT before running the query.