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!


About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook

7 comments:

GMTSlider.com said...

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

Anonymous said...

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

Anonymous said...

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

szolfaghari@gmail.com

soroush zolfaghari said...

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

soroush zolfaghari said...

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

Jim said...

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

Sally said...

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