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?
SQL Server 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
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.
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).
SUBSTRING(CONVERT(VARCHAR(34), SYSDATETIMEOFFSET()), 29, 5)
Returns (for example):
-06:0
Not 100% positive this will always work.
