Granularity of SQL datetime

I have a datetime co开发者_JAVA百科lumn in the db and when I test setting it

DateTime dateTime = DateTime.Now;
state.LastUpdated = dateTime;
Assert.AreEqual(dateTime , state.LastUpdated);

I get the following error

Assert.AreEqual failed. Expected:<3/2/2011 9:52:32 AM>. Actual:<3/2/2011 9:52:00 AM>.

What's the granularity of SQL datetime and is it possible to tune it for more granularity?

SQL Server is accurate to rounded increments of 0, 3 and 7 milliseconds http://msdn.microsoft.com/en-us/library/ms187819.aspx. You can't tune it for more granularity.

.Net DateTime is much more granular - smaller than milliseconds, it can also contain ticks. You need to keep this into account when asserting your test.

If you need more precision, you could always use a bigint in Sql Server instead of a DateTime, and store the number of ticks. (DateTime has a constructor that accepts an Int64 number of ticks.)

Use datetime2, which has up to 100 nanoseconds precision. msdn.microsoft.com/en-us/library/bb677335.aspx

Noted by @Remus in a comment, posting it as an answer for posterity.

SQL Datetime can represent a date down to fractions of a second,and, according to : http://msdn.microsoft.com/en-us/library/ms187819.aspx : datetime values are rounded to increments of .000, .003, or .007 seconds