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.