title: SQL, OPENXML and DateTime conversations
body: <p>I've inherited a mass of stored procedures that take as their only parameter a block of XML, the author then cracks it (using OPENXML) and then updates the a row.</p>
  <p>I've come across a problem while updating a table that has datetime columns, the following is a snippet of data that currently fails:</p>
  <pre><code>declare @stuff nvarchar (max);
  
  set @stuff =
  '&lt;Booking xmlns:xsi=&quot;http://www.w3.org/2001/XMLSchema-instance&quot; xmlns:xsd=&quot;http://www.w3.org/2001/XMLSchema&quot;&gt;  
    &lt;UpdatedDateTime&gt;2009-08-14T15:32:09.7240556+01:00&lt;/UpdatedDateTime&gt;
  &lt;/Booking&gt;';
  
  declare @iDoc int;
  
  exec sp_xml_preparedocument @idoc OUTPUT, @stuff;
  
  SELECT UpdatedDateTime
      FROM 
          OPENXML(@idoc, '/*')
      WITH
      (
          UpdatedDateTime datetime 'UpdatedDateTime'
      )
  </code></pre>
  <p>Running that causes the following error:</p>
  <blockquote>
  <p>Msg 241, Level 16, State 1, Line 12</p>
  <p>Conversion failed when converting
  datetime from character string.</p>
  </blockquote>
  <p>How do I go about converting the datetime from the XML fragment into a SQL based date time?</p>
  <p>Thanks</p>