title: Clone the value of a DATETIME column using arrays?
body: <p>I wrote the function below to get the values of two columns in a database, one is composed by integers (<code>ID</code>) and other dates/times in <code>DATETIME</code> format, in SQL Server 2008 R2. It stores the two values in two distinct arrays (lists), defined here:</p>
  
  <pre><code>  Dim arrPartIDs As New List(Of Integer)
    Dim arrPartUMDates As New List(Of Date)
  </code></pre>
  
  <p>My goal is to clone the datetime value of each row to the column 'Part_Previous_UMDate', which are currently set as NULL.</p>
  
  <p>However, when my code execute the line:</p>
  
  <pre><code>WinToolSQLCmd.ExecuteReader()
  </code></pre>
  
  <p>After </p>
  
  <pre><code> WinToolSQLCmd = New SqlCommand("UPDATE WTData.dbo.Parts SET Part_Previous_UMDate = '" &amp; arrPartUMDates.Item(intUMDateCounter) &amp; "' WHERE ID = " &amp; PartID.ToString, WinToolConnection)
  </code></pre>
  
  <p>The application returns an exception </p>
  
  <blockquote>
    <p>The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.</p>
  </blockquote>
  
  <p>I thought that by creating the array (list) <code>arrPartUMDates</code> as a list of date I could simply transfer its values back to the database, but apparently they are created as strings when added to the array...</p>
  
  <p>What's the best approach to clone the <code>DATETIME</code> value in <code>UMDate</code> to <code>Part_Previous_UMDate</code> with full precision? I need to have full precision because later I´ll compare the values of the two columns in order to determine if they need an update.</p>
  
  <p>Any ideas? I've searched for this error message but could not find anything related to dealing with dates in arrays...</p>
  
  <p>Image of table here: <a href="http://i.stack.imgur.com/BYT1j.png" rel="nofollow">http://i.stack.imgur.com/BYT1j.png</a></p>
  
  <p>Code:</p>
  
  <pre><code>Function MirrorUMDateInPartsTable()
  
          Dim arrPartIDs As New List(Of Integer)
          Dim arrPartUMDates As New List(Of Date)
          Dim WinToolConnection As New SqlConnection(StrGlobalWinToolConnection)
          Dim intUMDateCounter As Integer = 0
  
          WinToolSQLCmd = New SqlCommand("SELECT TOP 5 ID, UMDate FROM WTData.dbo.Parts WHERE WTData.dbo.Parts.Part_Previous_UMDate IS NULL ORDER BY ID", WinToolConnection)
          WinToolConnection.Open()
  
          Dim reader = WinToolSQLCmd.ExecuteReader()
          While reader.Read()
              arrPartIDs.Add(reader.Item(0))
              arrPartUMDates.Add(reader.Item(1))
          End While
          reader.Close()
          WinToolSQLCmd.Dispose()
  
          If arrPartIDs.Count &gt; 0 Then
              For Each PartID As Integer In arrPartIDs
                  WinToolSQLCmd = New SqlCommand("UPDATE WTData.dbo.Parts SET Part_Previous_UMDate = '" &amp; arrPartUMDates.Item(intUMDateCounter) &amp; "' WHERE ID = " &amp; PartID.ToString, WinToolConnection)
                  WinToolSQLCmd.ExecuteReader()
              Next
          End If
  
          Return Nothing
      End Function
  </code></pre>