title: Convert local datetime from xml to datetime in sql
body: <p>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.</p>
  
  <p>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.   </p>
  
  <p>Here a snippet of the XML with the date provided</p>
  
  <pre><code>&lt;Upload xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;
    &lt;DeviceID&gt;0008E02B66DD_&lt;/DeviceID&gt;
    &lt;DeviceType&gt;03.20&lt;/DeviceType&gt;
    &lt;FarmID&gt;2&lt;/FarmID&gt;
    &lt;UploadDate&gt;0001-01-01T00:00:00&lt;/UploadDate&gt;
    &lt;Sessions&gt;
      &lt;SessionID&gt;99&lt;/SessionID&gt;
      &lt;RecordedDate&gt;2012-02-03T13:00:00+13:00&lt;/RecordedDate&gt;
      &lt;Readings /&gt;
    &lt;/Sessions&gt;
  
  ...
  </code></pre>
  
  <p>Here is the snippet of my SQL code that processes it</p>
  
  <pre><code>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)
  </code></pre>
  
  <ul>
  <li>NOTE:  @UploadDate is actually the value from SQL getDate() i.e. my default</li>
  </ul>
  
  <p>The field in my database I am inserting this into is a <strong>datetime</strong> field</p>
  
  <p>Question:</p>
  
  <p>I want to insert the date received in the XML i.e. <strong>2012-02-03T13:00:00+13:00</strong> into a datetime field in my SQL database.  Is there anyway to convert this nicely in SQL?</p>