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!


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

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.