February 21, 2010

Convert From EST to GMT Time and vice versa in SQL Server




A user asked me if there were any functions to convert EST to GMT time in SQL Server. Well I do not think there is an existing function, but using the DATEADD() function, we should be able to achieve this easily. The difference between EST and GMT is -5 hours, so 4.00pm EST is will be 9.00pm GMT

The query would be like this:

DECLARE @ESTTime as DateTime
SET
@ESTTime = '2010-02-13 11:30:55.293'
SELECT DATEADD(HOUR, 5, @ESTTime) as GMTTime

DECLARE @GMTTime as DateTime
SET
@GMTTime = '2010-02-13 16:30:55.293'
SELECT DATEADD(HOUR, -5, @GMTTime) as ESTTime

OUTPUT

ESTToGMT

You can now easily create a user defined function using the above logic!


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

 
  Feedback:

comments

7 Responses to "Convert From EST to GMT Time and vice versa in SQL Server"
  1. GMTSlider.com said...
    September 30, 2010 at 12:29 PM

    I even can't think of a better chunk of code. Cool!

  2. Anonymous said...
    March 9, 2011 at 10:02 AM

    Sorry, but this does not cover DST periods. Use below code

    DECLARE @result int
    DECLARE @spring datetime
    DECLARE @autumn datetime

    -- 2nd sunday in march
    select @spring = cast( convert (varchar (4), year (@d)) + '-03-' + convert (varchar(2), 31- (floor (5 * year (@d) / 4) + 4) % 7) + ' 02:00:00' as datetime) -14
    if day(@spring)>14 set @spring = @spring -7
    -- first sunday in nov
    select @autumn = cast (convert (varchar (4), year (@d)) + '-10-' + convert (varchar(2), 31 -(floor (5 * year (@d) / 4) + 1) % 7) + ' 02:00:00' as datetime) + 7
    --print @spring
    --print @autumn
    if @d between @spring and @autumn select @Result = 4 else select @Result = 5

  3. Anonymous said...
    March 9, 2011 at 10:06 AM

    Above code tested for 2007 - 2013
    contact me if any questions

    szolfaghari@gmail.com

  4. soroush zolfaghari said...
    March 9, 2011 at 10:09 AM

    I put my code in a function:

    create function dbo.GetESTOffset(@dt DateTime) RETURNS int
    AS BEGIN
    --declare @d datetime
    --set @d = getdate()+365*4
    DECLARE @result int
    DECLARE @spring datetime
    DECLARE @autumn datetime

    -- 2nd sunday in march
    select @spring = cast( convert (varchar (4), year (@d)) + '-03-' + convert (varchar(2), 31- (floor (5 * year (@d) / 4) + 4) % 7) + ' 02:00:00' as datetime) -14
    if day(@spring)>14 set @spring = @spring -7
    -- first sunday in nov
    select @autumn = cast (convert (varchar (4), year (@d)) + '-10-' + convert (varchar(2), 31 -(floor (5 * year (@d) / 4) + 1) % 7) + ' 02:00:00' as datetime) + 7
    --print @spring
    --print @autumn
    if @d between @spring and @autumn select @Result = 4 else select @Result = 5
    --print @result
    return @result
    end


    please contact me if you have any question:

    szolfaghari@gmail.com

  5. soroush zolfaghari said...
    March 9, 2011 at 11:04 AM

    OK folks,

    I developed a nice and clean function, this time the input is GMT and returns EST.



    Create function dbo.GMT2EST(@d DateTime) RETURNS Datetime
    AS BEGIN
    --declare @d datetime
    --set @d = getdate()+365*4
    DECLARE @result int
    DECLARE @spring datetime
    DECLARE @autumn datetime

    -- 2nd sunday in march
    select @spring = cast( convert (varchar (4), year (@d)) + '-03-' + convert (varchar(2), 31- (floor (5 * year (@d) / 4) + 4) % 7) + ' 07:00:00' as datetime) -14
    if day(@spring)>14 set @spring = @spring -7
    -- first sunday in nov
    select @autumn = cast (convert (varchar (4), year (@d)) + '-10-' + convert (varchar(2), 31 -(floor (5 * year (@d) / 4) + 1) % 7) + ' 06:00:00' as datetime) + 7
    --print @spring
    --print @autumn
    if @d between @spring and @autumn select @Result = -4 else select @Result = -5
    --print @result
    return dateadd(hour,@Result,@d)
    end

    please contact me if any questions:
    szolfaghari@gmail.com

    TAG: GMT2EST TSQL

  6. Jim said...
    January 25, 2012 at 11:16 AM

    This seems to work for me:

    Create Function dbo.GMT2Current(@Date DateTime) RETURNS Datetime
    AS BEGIN
    return DateAdd(minute, DateDiff(minute,GetUTCDate(),GetDate()), @Date)
    END

  7. Sally said...
    January 14, 2014 at 1:43 PM

    Jim, that code won't work if the local system time is not in ET.

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions