mysql2000数据库溢出,错误 - SQLDATETIME溢出。必须是1753年1月1日12:00:00 AM和12/31/9999下午11:59:59之间...-优快云博客

I've been using this piece of code I've written and it's working in this most unclear manner. I wish to insert a row into the database which includes two columns of DateTime:

myrow.ApprovalDate = DateTime.Now

myrow.ProposedDate = DateTime.Now

And yet, when I update the database I receive this error:

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

I've even tried copying an inserted value from the database and hard code it into the object being updated:

// I copied this value from the DB

myrow.ApprovalDate = Convert.ToDateTime("2008-12-24 00:00:00.000");

Still same error, the strange part is that the above trick worked for the first insert to the DB but failed from there on. Any ideas what's going on?

解决方案

A DateTime in C# is a value type, not a reference type, and therefore cannot be null. It can however be the constant DateTime.MinValue which is outside the range of Sql Servers DATETIME data type.

Value types are guaranteed to always have a (default) value (of zero) without always needing to be explicitly set (in this case DateTime.MinValue).

Conclusion is you probably have an unset DateTime value that you are trying to pass to the database.

DateTime.MinValue = 1/1/0001 12:00:00 AM

DateTime.MaxValue = 23:59:59.9999999, December 31, 9999,

exactly one 100-nanosecond tick

before 00:00:00, January 1, 10000

Regarding Sql Server

datetime

Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds

smalldatetime

Date and time data from January 1, 1900, through June 6, 2079, with accuracy to the minute. smalldatetime values with 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999 seconds or higher are rounded up to the nearest minute.

Lastly, if you find yourself passing a C# DateTime as a string to sql, you need to format it as follows to retain maximum precision and to prevent sql server from throwing a similar error.

string sqlTimeAsString = myDateTime.ToString("yyyy-MM-ddTHH:mm:ss.fff")