title: Why does the datetime type in SQL Server Compact round values?
body: <p>I've run across something that surprised me.</p>
  
  <p>I am using <code>WinPcap</code> to collect data off a network.  Internally, <code>WinPcap</code> uses the Windows performance counters to generate its timestamps.  I know they are subject to drift, but these timestamps nevertheless have precision down to the microsecond level.</p>
  
  <p>If I insert these timestamps into a SQL Server Compact 4.0 database as a <code>datetime</code> value and extract them later, I noticed that the precision has dropped to milliseconds.</p>
  
  <p>For example, </p>
  
  <pre><code>10:52:19.706084 -&gt; 10:52:19.706000
  </code></pre>
  
  <p>Now, I have since read <a href="http://msdn.microsoft.com/en-us/library/ms172424%28v=sql.100%29.aspx" rel="nofollow">here</a> that SQL Server rounds values with the <code>datetime</code> type to .000, .003, or .007 milliseconds.  That explains what is happening.</p>
  
  <p>Now, the <code>datetime</code> field uses 8 bytes to store its data, 4 bytes for the date and 4 for the milliseconds since midnight.  But if I call <code>DateTime.ToBinary()</code>, I get back an 8-byte number that represents the value in all of its precision.  In fact, if I write this value to the database in a <code>bigint</code> column and then call <code>DateTime.FromBinary()</code> when extracting that value, I get the original value with the same precision.</p>
  
  <p>This is the approach I'm going to use, but I'm still curious:  Why didn't the original <code>datetime</code> type in SQL Server Compact use <code>DateTime</code>'s ToBinary/FromBinary storage mechanism?</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>As Aaron Bertrand rightly points out, SQL Compact does not support <code>datetime2</code>.  Further, <code>datetime2</code> uses 6, 7, or 8 bytes, not 54 bytes in regular SQL Server.  My basic question still stands, though.</p>