SQL Server Compact Minimum Date Value

Recently I got this error connecting to a SQL Server Compact database from .Net:

“An overflow occurred while converting to datetime.”

So I dug into my data insertion code (I was using the excellent Massive mini ORM by the way - and yes I know its not really an ORM but that’s another post) and found I was setting the offending DateTime field to DateTime.MinValue which it turns out SQL Compact doesn’t like. After a bit of investigation I found that whilst .Net supports a minimum date (DateTime.MinValue) of “01/01/0001 00:00:00” and a maximum (DateTime.MaxValue) of “31/12/9999 23:59:59” SQL Compact’s minimum date is “01/01/1753 00:00:00” (the same as SQL Server 2005 and before). This is the same for all current versions of SQL Server compact. For SQL Server 2008 upwards (2008 R2 and 2012) it’s recommended to use the DateTime2 data-type which supports the same range of dates as .Net.

To summarise:

	Version	Minimum Date	Maximum Date
.Net	All	01/01/0001 00:00:00	31/12/9999 23:59:59
SQL Server Compact	All	01/01/1753 00:00:00	31/12/9999 23:59:59
SQL Server (using datetime)	All	01/01/1753 00:00:00	31/12/9999 23:59:59
SQL Server (using datetime2)	2008 onwards	01/01/0001 00:00:00	31/12/9999 23:59:59

For a handy guide to the SQL Compact datatypes see here : http://msdn.microsoft.com….

For SQL Server datatypes see here : http://msdn.microsoft.com…

The System.Data.SqlTypes namespace contains the useful SqlDateTime struct which has MinValue and MaxValue properties so you can safely use SqlDateTime.MinValue instead of DateTime.MinValue in your code. For more information on SqlDateTime see here.