//  home   //  advanced search   //  news   //  categories   //  sql build chart   //  downloads   //  statistics
 ASP FAQ 
Home
ASP FAQ Tutorials

   8000XXXX Errors
   Alerts
   ASP.NET 2.0
   Classic ASP 1.0
      COM / ActiveX Components
      Forms
      General Topics
      Date/Time Routines
      Email Scripts & Info
      Files/Directories & FSO
   Databases
   General Concepts
   Search Engine Optimization (SEO)

Contact Us
Site Map

Search

Web
aspfaq.com
tutorials.aspfaq.com
classicasp.aspfaq.com

ASP FAQ Tutorials :: Classic ASP 1.0 :: Date/Time Routines :: How do I convert local time to UTC (GMT) time?


How do I convert local time to UTC (GMT) time?

Many people have asked how they can convert local times to UTC format
 

Current Date / Time
    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
 

Arbitrary Date / Time
    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
 

A Fun Use for a Calendar Table!
    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.

Related Articles

Can I get millisecond accuracy in ASP?
Can I make VBScript format dates for me?
Could I get a little help with dates?
Given a date, how do I find the beginning and end of that week?
Given two dates, how do I determine an age?
How do I calculate dates, such as the first day of the month?
How do I convert a DATEDIFF to days, hours, and minutes?
How do I convert a timespan, in seconds, to HH:MM:SS?
How do I count the number of business days between two dates?
How do I delimit/format dates for database entry?
How do I determine the number of seconds since 1/1/1970?
How do I display time in military format?
How do I implement a calendar / datepicker in ASP?
How do I select time only from a DATETIME column?
Why do I have problems inserting NOW() into a database?
Why does JavaScript's document.lastModified() not work in ASP files?

 

 


Created: 11/12/2001 | Last Updated: 9/13/2007 | broken links | helpful | not helpful | statistics
© Copyright 2006, UBR, Inc. All Rights Reserved. (361)

 

Copyright 1999-2006, All rights reserved.
Finding content
Finding content.  An error has occured...