title: Undesired rounding of DateTime in SQL Server
body: <p>I ran into something that seems odd. SQL Server appears to be rounding some <code>DateTime</code> values inappropriately when I save them to <code>datetime</code> columns. I suspect I'm missing something, but I can't spot it. I'm running this test against SQL Server 2008 using .NET 4.0. The following should illustrate the issue:</p>
  
  <p>I have created a table in in SQL Server called Timestamps. It has two columns:</p>
  
  <p><strong>id</strong> - bigint, Identity, PK<br>
  <strong>timestamp</strong> - datetime</p>
  
  <p>I also created a simple test that does the following:</p>
  
  <ol>
  <li>Gets the current time, truncating the value to millisecond precision</li>
  <li>Saved the truncated time to <code>Timestamps</code></li>
  <li>Retrieved the datetime` value from the DB and compared it to the original (truncated) DateTime object.</li>
  </ol>
  
  
  
  <pre><code>public static void RoundTest()
  {
      DateTime preTruncation = DateTime.UtcNow;
      DateTime truncated = preTruncation.TruncateToMilliseconds();
  
      using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["test"].ConnectionString))
      {
          conn.Open();
          SqlCommand cmd = new SqlCommand(@"INSERT INTO Timestamps(timestamp) 
                                              VALUES(@savedTime); 
                                              SELECT SCOPE_IDENTITY() AS id");
          cmd.Parameters.Add(new SqlParameter("savedTime", truncated));
          cmd.Connection = conn;
          var id = cmd.ExecuteScalar();
  
          SqlCommand get = new SqlCommand(@"SELECT timestamp FROM Timestamps 
                                              WHERE id = @id");
  
          get.Parameters.Add(new SqlParameter("id", id));
          get.Connection = conn;
          DateTime retrieved = (DateTime)get.ExecuteScalar();
  
          if (retrieved != truncated)
          {
              Console.WriteLine("original: " + preTruncation.TimeOfDay);
              Console.WriteLine("truncated: " + truncated.TimeOfDay);
              Console.WriteLine("retrieved: " + retrieved.TimeOfDay);
              Console.WriteLine();
          }
      }
  }
  </code></pre>
  
  <p>Although I expect the truncated value to be equivalent to the value returned back from the DB, that is not always the case.  Here's some sample output:</p>
  
  <pre><code>original: 19:59:13.4049965
  truncated: 19:59:13.4040000
  retrieved: 19:59:13.4030000
  
  original: 19:59:14.4989965
  truncated: 19:59:14.4980000
  retrieved: 19:59:14.4970000
  
  original: 19:59:15.4749965
  truncated: 19:59:15.4740000
  retrieved: 19:59:15.4730000
  
  original: 19:59:30.1549965
  truncated: 19:59:30.1540000
  retrieved: 19:59:30.1530000
  </code></pre>
  
  <p><code>TruncateToMilliseconds()</code> looks like this:</p>
  
  <pre><code>public static DateTime TruncateToMilliseconds(this DateTime t)
  {
      return new DateTime(t.Year, t.Month, t.Day, t.Hour, t.Minute, t.Second, t.Millisecond);
  }
  </code></pre>
  
  <p>What gives?  Is this really inappropriate rounding, or am I making a mistaken assumption here?</p>