Shifting to UTC time by using the datetimeoffset data type
As the world moves to a global economy, it becomes more and more important to capture time information with respect to Coordinated Universal Time (UTC). This format captures the date and time along with a time zone offset (offset values range from -14:00 to +14:00).
Many implementations prior to SQLServer 2008 do not have that information (The new variable precision datetime, datetimeoffset, DATE, and TIME data types were introduced in SQL Server 2008)..
As a request from one of our Independent Software Vendors (ISV) I created a Transact-SQL user-defined function to allow the conversion from the datetime data type to the datetimeoffset data type.
To make this conversion we must find the difference between UTC time and the current server time. We can then find the difference in hours and minutes (some daylight savings time rules allow for 15 minute increments) and append this information to the original datetime data. Please note that this is a point in time snapshot. Timezone information is subject to rules in each locality, and these rules can and do change on a irregular basis. This technique will not allow for historical look-back to calculate the daylight savings time rules.
So for example in March 2005 the United States daylight savings time rule changed. So a date prior to that (say March 2004) would only have the current rule applied by this technique. To solve the larger problem of historical accuracy of time zone information would require a database of all historical changes and is beyond the scope of this example.
I will outline the basic steps to do so, and will provide a complete sample for you to download.
CREATE FUNCTION [dbo].[ConvertToTZOffset](@current DATETIME)
RETURNS VARCHAR (34)
AS
BEGIN
-- if the input has full precision we can emit up to 34 characters of data
DECLARE @withTZ AS VARCHAR (34);
DECLARE @hour AS INT;
DECLARE @minute AS INT;
-- calculate the difference between UTC time and current server time
-- note: some daylight savings time rules are incremented in 15 minute intervals
SET @hour = CAST (DATEPART(hh, GETUTCDATE()) - DATEPART(hh, GETDATE()) AS INT);
SET @minute = CAST (DATEPART(mm,GETUTCDATE()) - DATEPART(mm, GETDATE()) AS INT);
-- get the current date and time
SET @withTZ = CONVERT (VARCHAR (34), @current, 121);
-- format with plus /minus sign which is required by datetimeoffset
IF @hour >= 0
SET @withTZ += '+';
ELSE
SET @withTZ += '-';
-- add leading zero, if required
IF ABS(@hour) < 10
SET @withTZ += '0';
SET @withTZ += CAST (ABS(@hour) AS VARCHAR (2));
SET @withTZ += ':';
-- same with minutes
IF @minute < 10
SET @withTZ += '0';
SET @withTZ += CAST (@minute AS VARCHAR (2));
RETURN @withTZ;
END
Once his function has been created, you can simply use the ALTER TABLE syntax to add a computed column that creates the datetimeoffset value by providing the function call as the default value like this:
ALTER TABLE dbo.timeZoneTest
ADD TZDate AS dbo.ConvertToTZOffset(rowDate);
Please see the attached sample for all the details.
boB 'The Tool Man' Taylor