Convert Time Zone:

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.