title: Insert DateTimes of format (yyyy-mm-dd HH:mm:ss +TT:TT) into SQL Server
body: <p>I am attempting to build parameters to insert date-times into a database as in:</p>
  
  <pre><code>InsertCommand.Parameters.AddWithValue("@TIME1", DateTime.Parse(fubardata[12]));
  </code></pre>
  
  <p>Where fubardata[12] contains "2015-07-22 13:41:53 +00:00" and is a list of strings</p>
  
  <p>I get when I execute the query:</p>
  
  <p><strong>Conversion Failed when converting date and/or time from character string.</strong> 
  The target column is of type Datetimeoffset(7)</p>
  
  <p><strong>NOTE: The below does not apply</strong> <em>the Database</em> isn't doing the formatting, .net is in my case.
  [As in <a href="https://msdn.microsoft.com/en-us/library/ms187819.aspx" rel="nofollow">https://msdn.microsoft.com/en-us/library/ms187819.aspx</a> this string format does not seem to be a supported type its also mentioned it is not supported by various posts on stack overflow.]</p>
  
  <p>I'm hard pressed to find the right cast, or how need to present a Datetime class so SQL parameters accept it. I've seen datetime database data presented to me in this format on several occasions. I'm sure there is a way.</p>
  
  <p>UPDATE: Casting as DateTimeOffset.Parse(fubardata[12]) still throws same exception when used in the paramter set syntax <code>InsertCommand.Parameters.AddWithValue("@TIME1", DateTimeOffset.Parse(fubardata[12]));</code></p>
  
  <p>UPDATE (SOLUTION): 
  I've written a small program on a test database that only contains an ID column and the column theTime DateTimeOffset(7) to test out writing these values to a database.
  in general, I have found the best way to write times to a database if they come to you in a non-standard string format is:</p>
  
  <pre><code>InsertCommand.Parameters.Add("@[your param]",SqlDbType.DateTimeOffset).Value = "[date as a string]";
  </code></pre>
  
  <p>FOLLOWS is the code I used to test it. I learned SQL can't handle a daytimeoffset / datetime created from a string of format "yyyy-mm-dd HH:mm:ss +-TT:TT" when addwithvalue is used. An important note is that if you table is using DateTimeOffsets, you should turn your datetimes to that DateTimeOffset type before inserting. As a side note I find it awkward that </p>
  
  <pre><code>InsertCommand.Parameters.AddWithValue("@[your param]", DateTime.Now)
  </code></pre>
  
  <p>-Causes no problems which people use all the time.</p>
  
  <pre><code>    private static void Main()
      {
  
          SqlConnection DataBaseWriterConnection;
          SqlCommand DataBaseWriterCommand;
          string SqlQueryString;
          List&lt;List&lt;string&gt;&gt; theData = new List&lt;List&lt;string&gt;&gt;();
          List&lt;string&gt; theDataRow1 = new List&lt;string&gt;();
          theDataRow1.Add("2015-07-22 13:41:00 +00:00");
          List&lt;string&gt; theDataRow2 = new List&lt;string&gt;();
          theDataRow2.Add("2015-07-22 13:42:00 +00:00");
          List&lt;string&gt; theDataRow3 = new List&lt;string&gt;();
          theDataRow3.Add("2015-07-22 13:43:00 +00:00");
          theData.Add(theDataRow1);
          theData.Add(theDataRow2);
          theData.Add(theDataRow3);
  
          DataBaseWriterConnection = new SqlConnection("Server = localhost; database = CA_TakacsTesting; connection timeout=30; Trusted_Connection = Yes");
          try
          {
              DataBaseWriterConnection.Open();
          }
          catch (Exception ex)
          {
              Console.WriteLine("Error connecting. Reason:\n" + ex.Message );
          }
          foreach (List&lt;string&gt; aDataRow in theData)
          {
              try
              {
                  SqlQueryString = "INSERT INTO DateTimeTesting (theTime) VALUES(@theDateTimeOffset)";
  
  
                  SqlCommand InsertCommand = new SqlCommand(SqlQueryString, DataBaseWriterConnection);
  
                  InsertCommand.Parameters.Add("@theDateTimeOffset", SqlDbType.DateTimeOffset).Value = aDataRow[0];
                  InsertCommand.ExecuteNonQuery();
                  Console.WriteLine("No Problems Caused");
                  Console.ReadLine();
              }
              catch (Exception ex)
              {
                  Console.WriteLine("Cannot Write for reason:\n" + ex.Message);
                  Console.ReadLine();
              }
  
          }
          DataBaseWriterConnection.Close();
      }
  }
  </code></pre>
  
  <p>The resulting database output is:</p>
  
  <pre><code>ID  theTime
  1   2015-07-22 13:41:00.0000000 +00:00
  2   2015-07-22 13:41:00.0000000 +00:00
  3   2015-07-22 13:42:00.0000000 +00:00
  </code></pre>
  
  <p>Which is what we want.</p>