SQL Server DateTimeOffset matching same "day"

Question

My application needs to collect "Tuesday's" purchases for all locations world wide, where "Tueday" is the location's Tuesday (regardless of time zone). And if the user needs to re-run the report next week, I need to still get "Last Tuesday's" data. All of our data is stored using DateTimeOffset.

So 9/4/12 00:00:00 -7 through 9/4/12 23:59:59 -7 must MATCH 9/4/12 00:00:00 +11 through 9/4/12 23:59:59 +11 when I am executing my WHERE clause.

I can't convert to UTC in the WHERE clause because that will pick up the data for "Tuesday" in London (depending on DST), not the location's Tuesday.

I tried converting from DateTimeOffset to DateTime, but that seems to convert to UTC. (In my tests, passing 9/1/12 through 9/30/12 picked up 8/31/12 data.)

Is there a trick to doing something like this with TSQL?

Thanks!

Answer 1

IMHO

DateTimeOffset = DateTime+Offset(from UTC)

So your data is already representing Client's Local date and time. Just cast it to DateTime and you will get the client's local Date and time.

But in-case if you want to add the Offset to the datetime and want the resultant Datetime then

DECLARE @PurchaseDate DATETIMEOFFSET(7) = CAST('2007-05-08 12:30:29.1234567 +5:00' AS  datetimeoffset(7)) 

SELECT  CAST(SWITCHOFFSET (@PurchaseDate , '+00:00') AS DATETIME)

have a look at this blog for further info.

http://blogs.msdn.com/b/bartd/archive/2009/03/31/the-death-of-datetime.aspx

Answer 2

When casting a DATETIMEOFFSET as DATETIME it takes the date and time as offset in the value and simply drops the time zone. The same is true when casting as DATE or TIME. So, I think you can simply cast the column as DATE and compare that to the date-only value you wish to match:

DECLARE @targetDate DATETIME2 = '2012-09-04' --Or we could use DATE here
SELECT [PurchaseId], [PurchaseTime], CAST([PurchaseTime] AS DATE) AS "PurchaseDate"
FROM [Purchases]
WHERE CAST([PurchaseTime] AS DATE) = @targetDate

I'm not sure how efficient this will be (hopefully not bad if the provider is truly clever--which SQL Server likely would be), but you might improve it by bounding the original column value as well:

DECLARE @targetDate DATETIME2 = '2012-09-04' --DATETIME2 so we can adjust by hours
SELECT [PurchaseId], [PurchaseTime], CAST([PurchaseTime] AS DATE) AS "PurchaseDate"
FROM [Purchases]
WHERE CAST([PurchaseTime] AS DATE) = @targetDate --Keep only the local-date matches
    AND [PurchaseTime] >= DATEADD(hh, -14, @targetDate) --Up to 14-hour time zone offset
    AND [PurchaseTime] <= DATEADD(hh, 38, @targetDate) --24 hours later plus 14

This should efficiently index down to the set of possibilities and then filter properly on the conversion to the local date. Note that time zone offsets can be up to 14 hours (New Zealand is furthest I know at +13:00, but they can go to +/- 14:00 according to MSDN) and the @targetDate will be at the start of the day, so it compares back to 14 hours earlier and 24+14=38 hours later. DATETIME2 has the same range and precision as DATETIMEOFFSET, so it's better for this purpose than the original DATETIME (but it may also work okay with DATETIME instead).