0

I have a method which takes in some parameters, among which there are three DateTime fields (populated from the Web Form).

The method uses an automatically generated TableAdapter and DataSet to add a new record onto a table. After the Table.Add[newRow] statement, we call the Dataset.Update(Table) statement to persist the addition onto the database.

While this works as expected for almost all cases, it sometimes gives an error: System.Data.SqlClient.SqlException (0x80131904): The conversion of a date data type to a datetime data type resulted in an out-of-range value The statement has been terminated..

Does anyone have a clue as to what may be happening?

spajce
  • 7,044
  • 5
  • 29
  • 44
user1012598
  • 417
  • 1
  • 8
  • 18

2 Answers2

2

From here

The date datatype can hold dates from Jan 1 of year 1 thru Dec 31, 9999. The datetime datatype can only hold dates from Jan 1, 1753 thru Dec 31, 9999. So you must have dates in your table with values of less than Jan 1, 1753.

Since DateTime is a struct, hence always has a value(DateTime.MinValue), another possible reason is that the DateTime could not be parsed successfully.

So you could check before you insert if the DateTime is in a valid range of SqlDateTime:

DateTime darkAge = new DateTime(1111, 1, 1);
if (darkAge >= SqlDateTime.MinValue.Value && darkAge <= SqlDateTime.MaxValue.Value)
{
    // we're not gettting here since the dark age was before 1753 
}
Tim Schmelter
  • 450,073
  • 74
  • 686
  • 939
  • 1
    Thanks for your swift response; it came a few minutes before I eventually cracked this, but you are on the right path. I really had to dive into the SQL statements as this was eventually an SQL issue, not a c# issue. Turns out the Insert was firing a trigger on the table which was running an update statement which was in turn using a custom function to get the date part from a DateTime; for this particular instance a record was manually added with a wrong date (0202-12-28 as opposed to 2012-12-28), and the function trying to extract the date part was throwing the error. – user1012598 Jan 11 '13 at 12:17
0

Yes it's becoz SQL DateTime and DotNet DateTime vary in allowed ranges.

SqlDateTime.MinValue != DateTime.MinValue, why?

Community
  • 1
  • 1
Nasmi Sabeer
  • 1,370
  • 9
  • 21