.net DateTime MaxValue is different once it is stored in database

When I store date property with value DateTime.MaxValue in database 开发者_Python百科and retrieve it back, the stored value does not equal to DateTime.MaxValue. The tick properties are off. Why is this?

Using MS SQL, data type for date field is 'datetime'

Because SQL datetime has lower resolution.

The DateTime data type in MS SQL represents Date and time data from January 1, 1753, to December 31, 9999, with an accuracy of one three-hundredth second, or 3.33 milliseconds. Values are rounded to increments of .000, .003, or .007 milliseconds.

source

The DateTime value type in .Net represents dates and times from 12:00:00 midnight, January 1, 0001 Anno Domini (Common Era) through 11:59:59 P.M., December 31, 9999 A.D. (C.E.) Time values are measured in 100-nanosecond units called ticks.

source

It could very well be cause a .NET DateTime doesn't directly translate to the SQL DateTime type.

I would both set the value and then check the ticks against SqlDateTime.MaxValue.

I must admit I'm not certain on this, but it could be to do with the accuracy of datetime?

Doing a quick search here is an article on the Precision and accuracy of DateTime

Also perhaps there is a mistmatch between the precision of datetime in c# vs sql?

Of what type is the data field in which you're storing this value?

It could be that the maximum value for a .NET DateTime exceeds the capacity for the equivalent data type in the database engine.

MS SQL Server does some odd things with dates at the lowest level. For example consider the following script:

select
    test1 = dateadd(ms,-1,convert(datetime,'20110504')),
    test2 = dateadd(ms,-2,convert(datetime,'20110504')),
    test3 = dateadd(ms,-3,convert(datetime,'20110504')),
    test4 = dateadd(ms,-4,convert(datetime,'20110504')),
    test5 = dateadd(ms,-5,convert(datetime,'20110504')),
    test6 = dateadd(ms,-6,convert(datetime,'20110504'))

This returns:

test1                   test2                   test3                   test4                   test5                   test6
----------------------- ----------------------- ----------------------- ----------------------- ----------------------- -----------------------
2011-05-04 00:00:00.000 2011-05-03 23:59:59.997 2011-05-03 23:59:59.997 2011-05-03 23:59:59.997 2011-05-03 23:59:59.993 2011-05-03 23:59:59.993

As you can see MS SQL is only dealing with milliseconds to the nearest 3. If you go between these they get rounded off. Is possible that this is what's happening when DateTime.MaxValue is getting stored in SQL.