ADO.NET Insert Min Value into SQL Server 2008 Date column crashes

Question

Hi,

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

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 2

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.