SQL datetime value

I have data formatted "2009-07-17T00:00:00-05:00" in varchar variable. How can I convert this data to datetime field in MS SQL server using query开发者_JAVA百科 or TSQL?

If you are on SQL Server 2008, you can use the datetimeoffset type:

select cast('2009-07-17T00:00:00-05:00' as datetimeoffset)

Since you are on 2005, datetimeoffset data type is not available for you. You should decide if you want to keep time zone information separately. If you just want the datetime part, just strip the time zone part from the string and cast it as a datetime:

select cast(left('2009-07-17T00:00:00-5:00', 19) as datetime)

Cast as DATETIME won't work, but that format is a valid XML datetime format, so you can route the cast through an XML type first:

declare @d varchar(50);
select @d = '2009-07-17T00:00:00-05:00';

select x.value(N'.', N'datetime') from (select cast(@d as xml) as x) as t;