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!
Did you like this post?
|
|
||
|
|
|
|
|
subscribe via rss |
|
subscribe via e-mail |
|
print this post |
|
follow me on twitter |
About The Author
comments
6 Responses to "Convert From EST to GMT Time and vice versa in SQL Server"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
Post a Comment