Using DateTimeOffset as WHERE criteria in a SQL Server 2008 query

Question

Hi Everyone,

I was wondering if anyone could help me understand why these two criteria do not return the same result sets. To me, it seems weird that SQL Server 2008 R2 wouldn't know to use the offset while constraining the data. Is there a better way to do this? As far as I can tell, Criteria Two is the only way to get the correct data.

-- Criteria One
OriginationDateTimeOffset >= TODATETIMEOFFSET('2010-10-20', '-08:00') AND
OriginationDateTimeOffset < TODATETIMEOFFSET('2010-10-21', '-08:00')

-- Criteria Two
SWITCHOFFSET(OriginationDateTimeOffset, '-08:00') >= TODATETIMEOFFSET('2010-10-20', '-08:00') AND
SWITCHOFFSET(OriginationDateTimeOffset, '-08:00') < TODATETIMEOFFSET('2010-10-21', '-08:00')

Answer 1

Why would it return the same? In criteria 1 you are comparing the original time to the offset, where in criteria 2 you are changing the offset on both.

Answer 2

TODATETIMEOFFSET converts the value(s) to datetimeoffset. SWITCHOFFSET changes the value to another datetimeoffset. For example:

DECLARE @OriginationDateTimeOffset DATETIMEOFFSET = '2010-10-20'

SELECT @OriginationDateTimeOffset, 
    SWITCHOFFSET(@OriginationDateTimeOffset, '-08:00'),
    TODATETIMEOFFSET(@OriginationDateTimeOffset, '-08:00')

produces:

2010-10-20 00:00:00.0000000 +00:00
2010-10-19 16:00:00.0000000 -08:00
2010-10-20 00:00:00.0000000 -08:00