CODEWITHSUNDEEP
sql-server
rgx71
rgx71

Reputation: 867

Best Practices working with Datetimeoffset

I already asked a similar question but I didn't get the answer I want. (It's my mistake!)

I have a website with a SQLServer 2008R2 database located in one country (US for example).

Many clients from different countries access to this website. All datetime datatypes are datetimeoffset. The datetime are saved using sql server datetime in US.

What should I do to convert the dates according to the client datetime zone and do I need to save the time zone different in a table to be able to convert the datetime from the database to the client?

Upvotes: 0

Views: 4225

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280580

I would store the data as UTC (and in fact I mandate that our servers are all set to UTC time with no DST adjustments - not only does this prevent gaps and overlaps twice a year, but it also makes job scheduling etc. much more predictable and reliable).

You can easily convert UTC data to any other time zone. You can use a calendar table to get the offset correctly and to account for things like DST. See how to convert all datetime columns in a sql server 2005 express database with data to UTC, Where to set a UTC datetime value in n-tier application: Presentation Layer, Domain, or Database?, How do I handle the timezones for every Chat message and http://web.archive.org/web/20070611150639/http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

Important: DATETIMEOFFSET is not DST-aware!

Upvotes: 1

Related Questions