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 or TSQL?
+3
A:
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)
Mehrdad Afshari
2009-10-12 21:52:19
somehow it's not working. shows "Type datetimeoffset is not a defined system type." What should I do?
THEn
2009-10-12 21:54:11
What SQL Server version you're using? `datetimeoffset` is new in 2008.
Mehrdad Afshari
2009-10-12 21:55:16
Microsoft SQL Server 2005 - 9.00.1406.00 (X64) Mar 3 2007 19:53:24 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
THEn
2009-10-12 21:56:03
I edited the question.
THEn
2009-10-12 22:00:01
+2
A:
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;
Remus Rusanu
2009-10-12 21:59:49
yes. this data is from XML file. I used SELECT [StartDate] FROMOPENXML(@iTree, '/List/DailyUpdate', 2) WITH ([STARTDATE] varchar(50)) any way to do it in SQL? I tried datetime it din't work...
THEn
2009-10-12 22:09:24
Can you use XML methods instead of OPENXML? OPENXML is a dinosaur... Just say `SELECT xmlfield.value(N'(/List/Daily/Update)[1]', N'datetime') FROM table`. With OPENXML you need to use the WITH syntax to specify the schema of the return and map StartDate to datetime: `SELECT StartDate FROM OPENXML(...) WITH (StartDate DATETIME)`.
Remus Rusanu
2009-10-12 22:17:16
Also the query in the answer keep showing "Conversion failed when converting datetime from character string." any idea ?
THEn
2009-10-12 22:46:18
I tested my query on SQL 2008 before I posted, but indeed the SQL 2005 XQuery/XPath parser does not recognize the time zone info. Unfortunately that pushes you back to a solution based on string truncate, like Mehrdad suggested.
Remus Rusanu
2009-10-12 23:22:01