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!

at

9 comments:

  1. GMTSlider.comSeptember 30, 2010 at 12:29 PM

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

    ReplyDelete
  2. AnonymousMarch 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

    ReplyDelete
  3. AnonymousMarch 9, 2011 at 10:06 AM

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

    szolfaghari@gmail.com

    ReplyDelete
  4. soroush zolfaghariMarch 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

    ReplyDelete
  5. soroush zolfaghariMarch 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

    ReplyDelete
  6. JimJanuary 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

    ReplyDelete
  7. SallyJanuary 14, 2014 at 1:43 PM

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

    ReplyDelete
  8. ShonApril 12, 2017 at 8:11 AM

    GETUTCDATE()

    ReplyDelete
  9. UnknownMay 2, 2017 at 3:41 AM

    GETUTCDATE() is good ,In the case when the content is for global readers/users just add Offset hours/minutes according to location in it.

    ReplyDelete