confusion around DateTimeOffset and TimeZones

Question

I am currently developing a .net application that will be used in multiple different countries and time zones. The back end database is sql server.

I have been looking in to the datetime, datetime2 and datetimeoffset data types and I am a little confused.

Some examples may help with my confusion. Lets say I have a table that has product data with a start and end date for its availability. So for the start and end date, the data gets saved as utc date and includes the time zone.

If I query the database and want to omit the records that are not between the start and and end date and would do something like:

select * from products where stardate <= getutcdate() and enddate >= getutcdate;

However this isnt valid as the utc date may not reflect the time in the country the data is required in ie. the time offset is not applied. So any ideas how I might work around this?

Next question I have around this is when the daylight time changes for that country, will the offset not be different? ie. will the data become stale as the offset value will be incorrect.

Moving up the layers in my application, and in to my .net code. When I retrieve the data from the database, i create a DateTimeOffset variable that contains the enddate/startdate. If I want to write some code similar to that in my sql to check the start and enddate, will I not run in to the same problems if I user DateTime.UtcNow?

Any help to assist with my confusion would be great.

If this is the start and end date for a product's availability, is time actually relevant, or do you just need to store the day that availability begins / ends? — Dan J
Yes it is as it based on timezones. So while it might be available in country it might not be in another for a period of hours. — amateur

Mike Beeler Mike Beeler · Accepted Answer · 2012-11-09T02:07:35

Utc dates are time zone and daylight savings time neutral and therefor all calculations on sql server should be performed in utc time only. Time zone information (locale) is stored on the local pc and is applied when converting from utc to local woithemes the date time class. For a web page the date is passed in as a utc string and converted to a local time Via date object (tolocaltime) times should be converted by either the web page or application before being sent to sql server

confusion around DateTimeOffset and TimeZones

1 Answers