ADO.NET Insert Min Value into SQL Server 2008 Date

Question

I'm trying to do the following:

using (var tx = sqlConnection.BeginTransaction())
{
 var command = sqlConnection.CreateCommand();
 command.Transaction = tx;
 command.CommandText = "INSERT INTO TryDate([MyDate]) VALUES(@p0)";
 var dateParam = command.CreateParameter();
 dateParam.ParameterName = "@p0";
 dateParam.DbType = DbType.Date;
 dateParam.Value = DateTime.MinValue.Date;
 command.Parameters.Add(dateParam);
 command.ExecuteNonQuery();
 tx.Commit();
}

where the table has a SQL Server 2008 Date column. I can insert a value of '01/01/0001' into this via SQL Management Studio.

If I run the above on the ExecuteNonQuery I get a "SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM." exception.

Why is this? The SQL Server Date field does indeed accept 01/01/0001.

Answer 1

DbType.Date is just an alias for DbType.DateTime, since it was conceived well before SQL Server 2008 had support for dates. You can see the whole story here: http://connect.microsoft.com/VisualStudio/feedback/details/646183/wrong-code-in-sqlparameter-dbtype-set-property.

Short story: as answered by Will, you can use SqlDbType.Date, which is not an alias.

Answer 2

This works...

var command = sqlConnection.CreateCommand();
command.Transaction = tx;
command.CommandText = "INSERT INTO TryDate([MyDate]) VALUES(@p0)";
SqlParameter dateParam = new SqlParameter();
dateParam.ParameterName = "@p0";
dateParam.SqlDbType = SqlDbType.Date;
dateParam.Value = DateTime.MinValue.Date;
command.Parameters.Add(dateParam);
command.ExecuteNonQuery();

Answer 3

As the error says Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

SQLServer DateTime columns have different min and max values than a dateTime variable in the CLR.

You may want to put logic in your program to protect the database from datetimes that are out of the SQLServer range.