views:

2431

answers:

5

We are dealing with an application that needs to handle global time data from different time zones and daylight savings time settings. The idea is to store everything in UTC format internally and only convert back and forth for the localized user interfaces. Does the SQL Server offer any mechanisms for dealing with the translations given a time, a country and a timezone?

This must be a common problem, so I'm surprised google wouldn't turn up anything usable.

Any pointers?

 Sören
A: 

Hi,

Yes, to some degree as detailed here.
The approach I've used (pre-2008) is to do the conversion in the .NET business logic before inserting into the DB.

AdaTheDev
+4  A: 

You can use GETUTCDATE() function to get UTC datetime Probably you can select difference between GETUTCDATE() and GETDATE() and use this difference to ajust your dates to UTC

But I agree with previous message, that it is much easier to control right datetime in the business layer (in .NET, for example).

Bogdan_Ch
A: 

SQL 2008 has a type called datetimeoffset. It's really useful for this type of stuff.

http://msdn.microsoft.com/en-us/library/bb630289.aspx

Then you can use the function SWITCHOFFSET to move it from one timezone to another, but still keeping the same UTC value.

http://msdn.microsoft.com/en-us/library/bb677244.aspx

Rob

Rob Farley
A: 

To add to Rob Farley's answer, here is an excellent write-up of hows/whys for using datetimeoffset datatype instead of datetime.

http://blogs.msdn.com/bartd/archive/2009/03/31/the-death-of-datetime.aspx

etliens