0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
GrahamB
  • 1,368
  • 15
  • 35
  • Try `dateParam.SqlDbType = SqlDbType.Date;` instead of `dateParam.DbType = DbType.Date;` - define and use the `SqlDbType` - that works for sure – marc_s Aug 14 '10 at 19:28

3 Answers3

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();
Will A
  • 24,780
  • 5
  • 50
  • 61
  • 1
    Reflecting the SqlParameter code shows that there's a definite distinction between DbType.Date and SqlDbType.Date - I've not found the particular line of code in the Framework that results in the error - however, using SqlDbType.Date should definitely solve this. – Will A Aug 14 '10 at 12:53
  • Thanks Will A. I suspected this would be the answer but really don't get why the "generic" DbType Date would fail with a SQL Server error. Especially since there is a also a DateTime DbType that I'd expect to match up to the original SQL DateTime type that have always had this restriction. Looks like a .NET bug to me. – GrahamB Aug 15 '10 at 00:41
0

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.

Gabriele Giuseppini
  • 1,541
  • 11
  • 19
0

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.

automatic
  • 2,727
  • 3
  • 34
  • 31
  • I initially thought this was it to, but he's using the newer `DATE` datatype. `DATE` and `DATETIME2` match the range of values allowed by the CLR. His problem appears to be in the ADO.NET call, not in SQL Server. His code must be inferring `DATETIME` instead of `DATE` somewhere. – mattmc3 Aug 14 '10 at 12:41
  • Nope - using DbType.Date rather than SqlDbType.Date is breaking the code and restricting the range. – Will A Aug 14 '10 at 12:43