Convert local datetime from xml to datetime in sql

sql, sql-server, xml

Solution

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 T.N.value('substring((RecordedDate/text())[1], 1, 19)', 'datetime'),
       T.N.value('(RecordedDate/text())[1]', 'datetime'),
       T.N.value('(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

Problem

I am receiving some data via xml that is to be inserted into an associated SQL table. The method I'm using to insert the data is by querying the XML in sql and doing a bulk insert as necessary. The problem I have is the date is never being recognized as a valid date and so is always going back to a default date. Here a snippet of the XML with the date provided ``` <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 the snippet of my 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 The field in my database I am inserting this into is a datetime field Question: I want to insert the date received in the XML i.e. 2012-02-03T13:00:00+13:00 into a datetime field in my SQL database. Is there anyway to convert this nicely in SQL?

Original source