Contact Us
Site Map

Search

Converting the current time is relatively simple, assuming that your server is set up correctly (proper time zone, and observes daylight savings time if appropriate). This is because the registry stores the offset between the local time zone and UTC. Here are a few examples: 
 
VBScript 
 
Assuming IUSR has read access to registry! If this is not the case, you can use the same logic as the VBScript example further on in this article. 
 


<%      od = now()      set oShell = CreateObject("WScript.Shell")      atb = "HKEY_LOCAL_MACHINE\System\CurrentControlSet\" &_          "Control\TimeZoneInformation\ActiveTimeBias"      offsetMin = oShell.RegRead(atb)      nd = dateadd("n", offsetMin, od)      Response.Write("Current = " & od & "<br>UTC = " & nd)  %>

 
JScript 
 


<script language="JScript" runat=server>        var od = new Date();      var nd = od.toGMTString();      Response.Write('Current = ' + od + '<br>UTC = ' + nd);    </script>

 
Transact-SQL 
 
In SQL Server 2000, you can get the current time in UTC format by using the GETUTCDATE() function: 
 


SELECT GETDATE() AS CurrentTime, GETUTCDATE() AS UTCTime

 
In SQL Server 7.0, you need to get this information from the registry (much like in VBScript): 
 


DECLARE @UTCOffset INTEGER    EXEC master..xp_regread      'HKEY_LOCAL_MACHINE',      'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',      'ActiveTimeBias',      @UTCOffset OUTPUT    SELECT DATEADD(MINUTE, @UTCOffset, GETDATE()) AS UTCTime

Converting an arbitrary time is a little more involved. Because the registry only stores the CURRENT bias, and doesn't keep historical record for previous dates, you may get invalid data if you are NOT in daylight savings time and you are converting a date that is (or vice-versa). These examples will show how to find GMT time for another date *in 2001* -- I will update this later to apply to any year. Note that for the VBScript and T-SQL solutions, you should know your regular offset (these examples assume Eastern time zone, which is 5 hours offset from UTC when *not* in DST). 
 
VBScript 
 


<%      ' fill in your known bias here!        offset = 5        ' tell us what date you want        od = "2005-02-01 5:32 AM"        ' find first Sunday in April        for i = 1 to 7          if weekday("4/" & i & "/" & year(d))=1 then              startDST = cdate("4/" & i & "/" & year(d))              exit for          end if      next        ' find last Sunday in October        for i = 31 to 25 step -1          if weekday("10/" & i & "/" & year(d))=1 then              endDST = cdate("10/" & i & "/" & year(d))              exit for          end if      next        ' subtract hour from offset if within DST        if cdate(od) >= startDST and cdate(od) < endDST then          offset = offset - 1      end if        nd = dateadd("h", offset, od)            Response.Write("Current = " & od & "<Br>UTC = " & nd)  %>

 
JScript 
 
A little smarter, JScript inherently knows when a date falls within DST, and adjusts accordingly. 
 


<script language="JScript" runat=server>        // note that months are 0-based in JScript      var od = new Date(2005,1,1,5,32,0);      var nd = od.toGMTString();      Response.Write('Current = ' + od + '<br>UTC = ' + nd);    </script>

 
Transact-SQL 
 


DECLARE      @offset TINYINT,      @dt SMALLDATETIME,      @sdt SMALLDATETIME,      @edt SMALLDATETIME,      DECLARE @i TINYINT    SELECT      @offset = 5,      @i = 1,      @dt = '20050201 05:32'    -- find first Sunday in April    WHILE @i < 7  BEGIN      SET @sdt = RTRIM(YEAR(@dt))+'040'+RTRIM(@i)      IF DATEPART(weekday,@sdt)=1       BEGIN          SET @i = 7      END      SET @i = @i + 1  END    -- find last Sunday in October    SET @i = 31  WHILE @i > 24  BEGIN      SET @edt = RTRIM(YEAR(@dt))+'10'+RTRIM(@i)      IF DATEPART(weekday,@edt)=1       BEGIN          SET @i = 24      END      SET @i = @i - 1  END    -- subtract hour from offset if within DST    IF (@dt>=@sdt AND @dt<@edt)      SET @offset = @offset - 1    SELECT @dt AS CurrentTime, DATEADD(hour, @offset, @dt) AS UTCTime

Now, a trick I have done in the past to avoid calculating this offset every time, is to store the offset in a table (see Article #2519 for a more thorough treatment of that topic). For most applications I've written, it's been useful to have a calendar table, with all foreseeable dates that the application will use. You can use this table for a variety of things, such as storing pre-calculated data points for each date (e.g. weekday number, weekday name, day of the year, quarter, week number, month name, and UTCOffset, whether it's a work day, weekend, holiday, etc). It's also quite useful for determining if a range of dates has gaps (or counting the number of work days between two dates) without having to process each day, one at a time. 
 
This specific example will only be useful for North American applications, since different parts of the world observe daylight savings time at unique points in the calendar year (if at all). Here, we know that on the first Sunday of April, at 2:00am, we set the clock to 3:00am, and on the last Sunday in October, at 2:00am, we set the clock to 1:00am. So, we can build and populate our table accordingly: 
 


CREATE TABLE dbo.Calendar  (      dt SMALLDATETIME PRIMARY KEY CLUSTERED,      UTCOffset TINYINT  )  GO    -- populate with data  DECLARE      @dt SMALLDATETIME,      @off TINYINT    SELECT      @dt = '20000101',      @off = 5    SET NOCOUNT ON    -- let's just do 10 years of days  WHILE @dt <= '20101231'  BEGIN      -- have we hit the first SUnday in April?      IF DATENAME(dw, @dt)='Sunday'          AND DATEPART(DAY, @dt) <= 7          AND DATENAME(MONTH, @dt) = 'April'              SET @off = 4        -- have we hit the last Sunday in October?      IF DATENAME(dw, @dt)='Sunday'          AND DATEPART(DAY, @dt) >= 25          AND DATENAME(MONTH, @dt) = 'October'              SET @off = 5        INSERT Calendar(dt, UTCOffset) VALUES(@dt, @off)            SET @dt = @dt + 1  END  GO

 
So now, we can build this function, which uses the table to determine the offset of the given day, and add those hours to it (again, easy in North America because we know that UTC is always a *positive* offset). 
 


CREATE FUNCTION dbo.getUTCDateTime  (      @dt DATETIME       )  RETURNS DATETIME  AS  BEGIN      SELECT TOP 1 @dt = DATEADD(HOUR, UTCOffset, @dt)          FROM Calendar WITH (NOLOCK)          WHERE dt <= @dt          ORDER BY dt DESC        RETURN @dt  END  GO    SELECT      dbo.getUTCDateTime('20030925'),      dbo.getUTCDateTime('20031021 04:32:55'),      dbo.getUTCDateTime('20031111 09:41:10')

 
Results: 
 


2003-09-25 04:00:00.000  2003-10-21 08:32:55.000  2003-11-11 14:41:10.000

 
It's a little trickier to program the reverse (getting the local time from a UTC datetime value), because if you happen to be on the day that DST changes, you need to know whether it's before or after 2:00am before you run the convert. I'll leave this reciprocal function as an exercise to the reader for now.