Recent Articles

DateTime.MinValue underflow in SQL Server

If you try to save DateTime.MinValue into a SQL Server "datetime" column, you'll get an overflow exception (sometimes referred as an underflow). Most people store MinValue to indicate a non-existent period in time instead of using DB Null or to indicate an initial moment in time where the actual date doesn't matter or couldn't make sense (eg. a similar MaxValue is often used to indicate account never expires).

The reason for the overflow is because .NET framework can cover a wider range of date than SQL Server. The DateTime structure in .NET supports a range from 0:00 1/1/0001 (MinValue) to 23:59 12/31/9999 (MaxValue). Whereas, SQL Server can only support a range of 1/1/1753 to 12/31/9999. Other databases handle datetime range differently. For instance, a PostgreSQL database can comfortably support the full DateTime range.

To workaround the problem, you can use SqlDateTime.MinValue instead to set DateTime.MinValue. To convert back into standard DateTime,s imply convert it back to DateTime structure like this:

DateTime date = Convert.ToDateTime(SqlDateTime.MinValue);

There is no standard in allowable date range between languages (Java, .NET, PHP) and databases (MySQL, SQL Server, Oracle, PostgreSQL). So pay attention when you pass a date range in code and especially in web services.