We are building an Operational Data Store (ODS). The data is coming from our production databases which are located in the United States and Asia. The data in our production databases store date/time values as local time (no UTC or offsets). When we bring the data into our ODS, we're storing all date/time values as DateTimeOffset values.

When our users query the ODS data, we don't want them to have to think about including the appropriate offset value in their queries involving date/time values. We want them to be able to query the data as if it were simple local time values.

We're masking the offset values on date/time values by having users query views. The views return local time values and strip off the offset values. While this works, any indexes on the underlying datetimeoffset columns are not used because the act of converting the datetimeoffset to datetime in the views causes indexes to not be used.

So, I'm looking for advice on how others, who have dealt with this issue, have dealed with it. We need to be able to utilize indexes while, at the same time, not require users to think about time zones and daylight savings time when querying the data.

We're using SQL Server 2012.

  • 1
    Store as UTC. Then everything is at par and you can always convert to local time zone when you need to (and the presentation side can handle that, as well as handle converting seek parameters from the client's time zone). One weakness you may not have noticed yet abut DateTimeOffset: it is not DST-aware. Aug 20, 2013 at 14:36
  • @AaronBertrand - Thanks. I don't see how storing UTC solves my problem. When users query the ODS UTC data they would need to know how to convert the UTC values to local time to query the data correctly. If a user wants to see all rows where some value is, say, greater than 8/20/2013 5:00:00 AM, they are thinking in terms of local time, but the data is UTC. They would have to convert their query value to UTC first. Or, the UTC needs to be converted to local time, but then any index on the column won't get used. Aug 20, 2013 at 14:45
  • Can't the application convert for them? It knows the difference between their time zone and UTC. Another idea is to use a calendar table, which is a solution I used in the past for an application developed long before DateTimeOffset even existed, and when time zone handling in C# was much less mature. You have a function that returns the offset based on TimeZone and Date, and add/subtract the number of minutes before passing that value to the actual query. The user doesn't have to think. Aug 20, 2013 at 14:47
  • @AaronBertrand - There is no application. This is an ODS that users will be querying directly for ad-hoc queries, reporting, and other sorts of data analysis. Aug 20, 2013 at 14:49
  • 2
    And, again, you're not going to be able to use datetimeoffset to magically solve this, and particularly without some additional work, unless you don't care about DST shifts. Aug 20, 2013 at 15:09

1 Answer 1


We solved this in Oracle using function based indexes. Although, it's not quite as flexible, you can accomplish similar in SQL Server using computed columns.

If all of the information you need is in the table, and you meet all of the ownership requirements (outlined in the second article) then I would recommend making the date/time field a computed column on the table, and then add an index to the computed column.

Computed Columns:


Indexing Computed Columns:


  • 1
    So are you recommending a computed, indexed column for every possible timezone any user might be in? Aug 20, 2013 at 14:50
  • Interesting. This might work. I'll have to study this. Aug 20, 2013 at 14:52
  • @Aaron Bertrand: Yes, that's correct. In our case, there were only 2 possibles. ET and GMT, so that was manageable.
    – McSenstrum
    Aug 20, 2013 at 14:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.