Convert Time Zone:
I have 05/02/2007 10:00AM CST, how can I convert this to EST in SQL Server 2000 function?
Code Snippet
--USAGE:
select dbo.udf_ConvertTime('January 1, 2005 12:00 PM','PDT','EST')
go
create function udf_ConvertTime(
@.TimeToConvert datetime
,@.TimeZoneFrom varchar(4)
,@.TimeZoneTo varchar(4)
)
returns DateTime
as
begin
declare @.dtOutput datetime, @.nAdjust smallint
declare @.temp table(TimeZone varchar(4),nOffset smallint)
insert into @.Temp select 'A',60
insert into @.Temp select 'ACDT',630
insert into @.Temp select 'ACST',570
insert into @.Temp select 'ADT',-180
insert into @.Temp select 'AEDT',660
insert into @.Temp select 'AEST',600
insert into @.Temp select 'AKDT',-480
insert into @.Temp select 'AKST',-540
insert into @.Temp select 'AST',-240
insert into @.Temp select 'AWDT',540
insert into @.Temp select 'AWST',480
insert into @.Temp select 'B',120
insert into @.Temp select 'BST',60
insert into @.Temp select 'C',180
insert into @.Temp select 'CDT',-300
insert into @.Temp select 'CEDT',120
insert into @.Temp select 'CEST',120
insert into @.Temp select 'CET',60
insert into @.Temp select 'CST',630
insert into @.Temp select 'CST',570
insert into @.Temp select 'CST',-360
insert into @.Temp select 'CXT',420
insert into @.Temp select 'D',240
insert into @.Temp select 'E',300
insert into @.Temp select 'EDT',-240
insert into @.Temp select 'EEDT',180
insert into @.Temp select 'EEST',180
insert into @.Temp select 'EET',120
insert into @.Temp select 'EST',660
insert into @.Temp select 'EST',600
insert into @.Temp select 'EST',-300
insert into @.Temp select 'F',360
insert into @.Temp select 'G',420
insert into @.Temp select 'GMT',0
insert into @.Temp select 'H',480
insert into @.Temp select 'HAA',-180
insert into @.Temp select 'HAC',-300
insert into @.Temp select 'HADT',-540
insert into @.Temp select 'HAE',-240
insert into @.Temp select 'HAP',-420
insert into @.Temp select 'HAR',-360
insert into @.Temp select 'HAST',-600
insert into @.Temp select 'HAT',-150
insert into @.Temp select 'HAY',-480
insert into @.Temp select 'HNA',-240
insert into @.Temp select 'HNC',-360
insert into @.Temp select 'HNE',-300
insert into @.Temp select 'HNP',-480
insert into @.Temp select 'HNR',-420
insert into @.Temp select 'HNT',-210
insert into @.Temp select 'HNY',-540
insert into @.Temp select 'I',540
insert into @.Temp select 'IST',60
insert into @.Temp select 'K',600
insert into @.Temp select 'L',660
insert into @.Temp select 'M',720
insert into @.Temp select 'MDT',-360
insert into @.Temp select 'MESZ',120
insert into @.Temp select 'MEZ',60
insert into @.Temp select 'MST',-420
insert into @.Temp select 'N',-60
insert into @.Temp select 'NDT',-150
insert into @.Temp select 'NFT',690
insert into @.Temp select 'NST',-210
insert into @.Temp select 'O',-120
insert into @.Temp select 'P',-180
insert into @.Temp select 'PDT',-420
insert into @.Temp select 'PST',-480
insert into @.Temp select 'Q',-240
insert into @.Temp select 'R',-300
insert into @.Temp select 'S',-360
insert into @.Temp select 'T',-420
insert into @.Temp select 'U',-480
insert into @.Temp select 'UTC',0
insert into @.Temp select 'V',-540
insert into @.Temp select 'W',-600
insert into @.Temp select 'WEDT',60
insert into @.Temp select 'WEST',60
insert into @.Temp select 'WET',0
insert into @.Temp select 'WST',540
insert into @.Temp select 'WST',480
insert into @.Temp select 'X',-660
insert into @.Temp select 'Y',-720
insert into @.Temp select 'Z',0
select @.nAdjust = nOffset
from @.Temp
where timeZone = @.TimeZoneFrom
select @.nAdjust = @.nAdjust - nOffset
from @.Temp
where timeZone = @.TimeZoneTo
set @.dtOutput = dateadd(n,@.nAdjust,@.TimeToConvert)
return @.dtOutput
end
|||Thanks you for your response. Can you give me some explanation about the nOffset, what is the logic behind these numbers and if this would work for daylight saving time period?
|||Use DATEADD()
SELECT dateadd( hour, -1, CSTColumn ) AS 'EST'
FROM MyTable
|||nOffset is the "minutes from GMT" for each time zone. I convert to minutes because world-wide, there are several time zones that are x Hours and y Minutes from GMT. So, I've converted everything to minutes.
EST is -300 minutes from GMT ( -5:00)
EDT is -240 Minutes from GMT ( -4:00)
You have to already know whether your source time is Daylight time or not. (EDT is different than EST).
So, you pass in a "Source Time" and a Source Time Zone and a Target Time Zone.
