8

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!

DeborahK
  • 57,520
  • 12
  • 104
  • 129

2 Answers2

2

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

ClearLogic
  • 3,616
  • 1
  • 23
  • 31
  • 2
    `SWITCHOFFSET` adjusts a `DATETIMEOFFSET` to a different time zone but it still refers to the same actual moment in time. I believe your SELECT example will actually just show the UTC time of the original purchase, not the local time as required. (The first part of your answer is correct for the question. The second part is perhaps unrelated but could be misleading for the question as asked.) – Rob Parker Sep 10 '13 at 20:34
  • How the hell can this be done in Entity Framework 6? There seems to be no way whatsoever to get at or compare the DateTime part of a DateTimeOffset. It doesn't support DateTime.Date nor DateTimeOffset.DateTime, so there's no way to do this cast. The closest thing to it is DbFunctions.TruncateTime (but no TruncateOffset) and it generates nasty SQL anyway. – Triynko Dec 14 '16 at 17:39
1

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).

Rob Parker
  • 4,078
  • 1
  • 25
  • 26
  • How does this impact indexes on the DateTimeOffset field? As soon as you cast the column, doesn't that prevent the index from being used? I'm starting to think DateTimeOffset is useless, and we're better off just storing both the local DateTime and the UTC DateTime. – Triynko Dec 14 '16 at 17:40
  • 1
    @Triynko, that's why I suggested the bounding on the original column value. My example uses a DATETIME2 initialized as a simple date (meaning Midnight starting that date) and adjusted by the maximum time zone offset at both ends to narrow the `[PurchaseTime] DATETIMEOFFSET` column (presumably indexed) to the range the `@targetDate` could fall within for any local purchase time. DATETIMEOFFSET are compared to each other by absolute (UTC) time not by their local representation of time, so it should work. The local conversion test applies to those matches. Experiment further if you're curious. – Rob Parker Dec 16 '16 at 20:43