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)