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.

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?

SQL Server has no idea about timezones. If you know the date you have is CST and you need to convert it to EST, you need use the datefunctions to add an hour.

DATEADD(Hour, 1, Yourdate)

|||Ok. Thanks, what about other timezones in US?|||same thing.. you can add or subtract accordingly. +1 or -2 etc.

Convert string into datetime with timezone

I have tried to execute the following sql statement to convert a string with time zone information to datetime without success:

SELECT CONVERT(datetime, '2007-07-24T14:00:00+07:00', 126);

Is there any advice? I'm SQL Server Express 2005 as follows:

Microsoft SQL Server Management Studio Express 9.00.3042.00
Microsoft Data Access Components (MDAC) 6.0.6000.16386 (vista_rtm.061101-2205)
Microsoft MSXML 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 7.0.6000.16473
Microsoft .NET Framework 2.0.50727.1318
Operating System 6.0.6000

Thanks,

Jong

You can only parse it. SQL doesn't recognize that as a legit date format.

You might try this:

Code Snippet

declare @.strDate varchar(255)

set @.strDate = '2007-07-24T14:00:00+07:00'

select convert(datetime,left(@.strDate,len(@.strDate) - patindex('%[+-]%',reverse(@.strDate))))

YOu have to reverse the string before Patindex becuase you need to allow for + or - and of course, there are dashes after the year.

Alternativly, if you can always rely on the exact format:

Code Snippet

declare @.strDate varchar(255)

set @.strDate = '2007-07-24T14:00:00+07:00'

select convert(datetime,left(@.strDate,19))

Neither of these converts the time to GMT...you would have to further parse and then use DateAdd.|||

Here is an illustration of one possible way to handle this issue.

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
MyDate varchar(30)
)

INSERT INTO @.MyTable VALUES ( '2007-07-24T14:00:00+07:00' )
INSERT INTO @.MyTable VALUES ( '2007-07-24T14:00:00+11:00' )
INSERT INTO @.MyTable VALUES ( '2007-07-24T14:00:00-02:00' )
INSERT INTO @.MyTable VALUES ( '2007-07-24T14:00:00-11:00' )

SELECT
RowID,
LocalTime = dateadd( hour, cast( substring( MyDate, 20, 3 ) AS int ), convert( datetime, left( MyDate, 19 )))
FROM @.MyTable

RowID LocalTime
-- --
1 2007-07-24 21:00:00.000
2 2007-07-25 01:00:00.000
3 2007-07-24 12:00:00.000
4 2007-07-24 03:00:00.000

|||

rusag2 wrote:

You can only parse it. SQL doesn't recognize that as a legit date format.

I thought SQL Server 2005 should recognize it as instructed in http://msdn2.microsoft.com/en-us/library/ms187928.aspx. I'm not sure if there is any difference between SQL Server 2005 and SQL Server Express 2005.

Anyway, thanks for answering.

|||

SQL Server 2005 Xquery includes support for dateTime values with timezone information. So you can use the built-in xquery functions to do the appropriate conversion. You simply pass the datetime string value to one of the xquery methods like value and do the conversion. Below are some examples:

Code Snippet

select cast('' as xml).value('xs:dateTime("2007-07-24T14:00:00+07:00")', 'datetime')

declare @.d varchar(30);
set @.d = '2007-07-24T14:00:00+07:00'
select cast('' as xml).value('xs:dateTime(sql:variable("@.d"))', 'datetime')

select cast('' as xml).value('xs:dateTime(sql:column("t.d"))', 'datetime')

from (select '2007-07-24T14:00:00+07:00') as t(d)