Convert local datetime from xml to datetime to sql

Question

I get some data through xml that needs to be inserted into a linked SQL table. The method that I use to insert data is to query the XML in sql and, if necessary, does a bulk insert.

The problem I have is a date that is never recognized as a valid date and therefore always reverts to the default date.

Here is an XML snippet with a date

<Upload xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <DeviceID>0008E02B66DD_</DeviceID> <DeviceType>03.20</DeviceType> <FarmID>2</FarmID> <UploadDate>0001-01-01T00:00:00</UploadDate> <Sessions> <SessionID>99</SessionID> <RecordedDate>2012-02-03T13:00:00+13:00</RecordedDate> <Readings /> </Sessions> ... 

Here is a snippet of SQL code that processes it

 Select (CASE WHEN ISDATE(s.value('(RecordedDate)[1]', 'varchar(50)')) = 1 THEN s.value('(RecordedDate)[1]', 'varchar(50)') ELSE @UploadDate END) DateOfMeasurement INTO #Session FROM @XMLData.nodes('/Upload') AS Upload(u) CROSS APPLY u.nodes('./Sessions') Sessions(s) 

• NOTE: @UploadDate is actually the value from SQL getDate (), i.e. my default

Field in my database I insert this into the datetime field

Question:

I want to insert the date retrieved in XML ie 2012-02-03T13: 00: 00 + 13: 00 into the datetime field in my SQL database. Is there any way to convert this to SQL?

Answer 1

 declare @XMLData xml = ' <Upload xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <DeviceID>0008E02B66DD_</DeviceID> <DeviceType>03.20</DeviceType> <FarmID>2</FarmID> <UploadDate>0001-01-01T00:00:00</UploadDate> <Sessions> <SessionID>99</SessionID> <RecordedDate>2012-02-03T13:00:00+13:00</RecordedDate> <Readings /> </Sessions> </Upload>'; select TNvalue('substring((RecordedDate/text())[1], 1, 19)', 'datetime'), TNvalue('(RecordedDate/text())[1]', 'datetime'), TNvalue('(RecordedDate/text())[1]', 'datetimeoffset') from @XMLData.nodes('/Upload/Sessions') as T(N); 

Result:

 2012-02-03 13:00:00.000 2012-02-03 00:00:00.000 2012-02-03 13:00:00.0000000 +13:00