Casting DateTimeOffset to DateTime and comparing results to DateTime

Question

Sometimes the CASTing a DateTimeOffset to Datetime appears to take the DateTimeOffset field back to UTC

I would like to find all orders that occur between particular dates. The OrderDateTime is stored as DateTimeOffset.

DECLARE @StartDate DATETIME = '20130723'
       ,@EndDate   DATETIME = '20130724'
      
SELECT cn.orderdatetime,
       LocalTime = CAST(orderdatetime AS datetime),
       facilityid
FROM ConsignmentNote cn
WHERE CAST(OrderDateTime AS DATETIME) BETWEEN @StartDate AND @EndDate

The results for this query is (as you would expect)

OrderDateTime                         LocalTime                  Facilityid
2013-07-23 08:26:02.9120644 +10:00    2013-07-23 08:26:02.913    84
2013-07-23 08:27:43.9571506 +10:00    2013-07-23 08:27:43.957    84
2013-07-23 10:24:54.2930893 +10:00    2013-07-23 10:24:54.293    84

But I need to also filter this result set on the facilityID - but if I add the facilityId to the query:

DECLARE @StartDate DATETIME = '20130723'
       ,@EndDate   DATETIME = '20130724'
      
SELECT cn.orderdatetime,
       LocalTime = CAST(orderdatetime AS datetime),
       facilityid
FROM ConsignmentNote cn
WHERE CAST(OrderDateTime AS DATETIME) BETWEEN @StartDate AND @EndDate
AND FacilityId = 84

I get the following results

orderdatetime                       LocalTime               facilityid
2013-07-23 10:24:54.2930893 +10:00  2013-07-23 10:24:54.293 84

what gives? why does adding another param to the query screw with the dates? (nb facilityID is int in consignmentNote Table)

Just to prove that point - if I move the StartDate back a day to '20130722' I get the 3 rows of results I am looking for, which seems to indicate that:

 CAST (OrderDateTime as DateTime) 

is (sometimes?) treated differently depending whether its in the SELECT or the WHERE clause, or there are other parameters?? (well it doesn't seem a uniform treatment)

Can anyone point me in any direction to trouble shoot this issue? Could it be service pack or a hotfix

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

btw - I know that if I create a table with just these values in - it all just works as you'd expect (code below) without any problems - so it has to be environmental - yes?

CREATE TABLE #temp (orderdatetime DATETIMEOFFSET,facilityid int)
INSERT INTO #temp VALUES ('2013-07-23 08:26:02.9120644 +10:00',84)
INSERT INTO #temp VALUES ('2013-07-23 08:27:43.9571506 +10:00',84)
INSERT INTO #temp VALUES ('2013-07-23 10:24:54.2930893 +10:00',84)

SELECT     orderdatetime,CAST(orderdatetime AS datetime),facilityid
FROM #temp 
WHERE    CAST(OrderDateTime AS DATETIME) BETWEEN @StartDate AND @EndDate
AND facilityid =84

DROP TABLE #temp

Answer 1

This guy was trying to do the opposite of what you've stumbled upon. That is, he was trying to convert his datetimeoffsets to UTC. But there's some discussion on casting of this type I think you will find relevant.

In testing the following:

DECLARE @Something datetimeoffset(7)

SET @Something = '2008-12-19 17:30:09.1234567 +11:00'

SELECT 1
WHERE DATEPART(hour, convert(datetime, @Something)) = 17

select cast(@Something as datetime)
select convert(datetime, @Something, 109)

It appears that CONVERT will not change the timezone to UTC in the WHERE clause and also will not change the timezone in the SELECT clause. However, using any style in the conversion will change to UTC.

Hope this helps.

Answer 2

Do you have an index on that column? If so, I think you are seeing another variation to the problem described in KB2715289.

According to that article, the problem was fixed in:

• Cumulative Update Package 7 for SQL2008R2SP1
• Cumulative Update Package 1 for SQL2008R2SP2
• Cumulative Update Package 3 for SQL2012

I would suggest applying the latest updates either manually or through Microsoft Update and see if you still have the problem.