What's a good way to check if two datetimes are on the same calendar day in TSQL?
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.
whereyear(date1) = year(date2)and month(date1) = month(date2)and day(date1) = day(date2)