How To Convert SQL Server datetime Data To UTC Time

Written by boB Taylor, Principal Microsoft Premier Field Engineer.

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 an 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);

Here’s a sample with all the details: DemonstrateTZoffset.sql.zip

boB 'The Tool Man' Taylor