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
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
9 comments:
I even can't think of a better chunk of code. Cool!
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
Above code tested for 2007 - 2013
contact me if any questions
szolfaghari@gmail.com
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
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
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
Jim, that code won't work if the local system time is not in ET.
GETUTCDATE()
GETUTCDATE() is good ,In the case when the content is for global readers/users just add Offset hours/minutes according to location in it.
Post a Comment