title: Best way to store date/time data in SQL Server 2008 R2 and SQL Server Compact 4
body: <p>We are developing an application in C# 4 that uses SQL Server 2008 R2 as backend. SQL Server Compact 4 is also used for disconnected clients in a very few rare scenarios. We are wondering what's the best way to store date/time data into these databases so that:</p>
  
  <ol>
  <li>Data containing different time offsets (coming from different time zones) can co-exist nicely. This means being sorted and compared.</li>
  <li>Data in SQL Server 2008 R2 and SQL Server Compact 4 can be transferred back and forth seamlessly; this is a secondary requirement that should not compromise the chosen design.</li>
  </ol>
  
  <p>Our main concern is to preserve the local time for each recorded event, but without losing the ability to compare and sort events that have been generated from different time zones and therefore have different time offsets.</p>
  
  <p>We have considered the <code>datetimeoffset</code> data type, since it stores the time offset and because it maps nicely to .NET's <code>DateTimeOffset</code>. However, it is not supported in SQL Server Compact 4. An alternative would be to remove offset info from the database and use a simple <code>datetime</code> data type, so that every piece of data in the database is normalized, and the issues with Compact are fewer. However, this introduces the problem that offset info would need to be reconstructed somehow on retrieval before the user sees the data.</p>
  
  <p>So my question is, are there any best practices or guidelines on how to store date/time values in SQL Server, taking into account that we will need to deal with different time zones, and making the interoperability between 2008 R2 and Compact 4 as easy as possible?</p>
  
  <p>Thank you.</p>