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 DateTimeSET @ESTTime = '2010-02-13 11:30:55.293'SELECT DATEADD(HOUR, 5, @ESTTime) as GMTTimeDECLARE @GMTTime as DateTimeSET @GMTTime = '2010-02-13 16:30:55.293'SELECT DATEADD(HOUR, -5, @GMTTime) as ESTTime`

OUTPUT

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

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.

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

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
end

szolfaghari@gmail.com

TAG: GMT2EST TSQL

Jim said...

This seems to work for me:

Create Function dbo.GMT2Current(@Date DateTime) RETURNS Datetime
AS BEGIN