0

I am writing a unit test which stores an object with a DateTime parameter into a DATETIME2 SQL Server database column. I then create a temporary DateTime object called new_date_time and set that value to DateTime.Now.

The new_date_time value is then used to update the previous value and the SQL query to do this completes successfully.

When re-reading the object back from the database I receive the correct datetime values for days/hours/minutes but the .Ticks value is different from the new_date_time variables .Ticks property. The value returned from the read call returns the last 4 digits of the .Ticks property as zeros.

Why is this rounding occurring making my Assert.AreEqual fail?? :)

Thanks

Improve this question
8
  • Does your unit test cause a query against the database? Your unit test should not depend on the database or change the contents. See this post. Commented Aug 26, 2014 at 22:10
  • yes I am querying the database, its using a temp variable of type datetime to verify that the update command is updating the correct data in the database Commented Aug 26, 2014 at 22:27
  • How is the DateTime2 column defined? Are you using just datetime2 or datetime2(n)? Commented Aug 26, 2014 at 22:41
  • datetime2 default precision is allegedly max precision Commented Aug 26, 2014 at 22:44
  • Do you use Getdate (or getutcdate) in your SQL? Commented Aug 26, 2014 at 22:45

2 Answers 2

Reset to default
3

I guess you are using Parameters.AddWithValue when writing the date to Sql Server. From MSDN the inferred type of a CLR DateTime is SqlDbType.DateTime and not SqlDbType.DateTime2 so the precision is being lost when writing your date to the database.

Explicitly setting the type to datetime2 will solve the issue. For example:

command.Parameters.AddWithValue("@now", DateTime.Now).SqlDbType = 
                                                       SqlDbType.DateTime2;

Edit

@marc_s makes a good point with his comment:

You should read (and embrace!) Can we stop using AddWithValue() already?

To avoid these kind of issues from biting you, you could get into the habit of using the Add method on the parameters collection which takes the SqlDbType in some overloads and then set the Value property on that rather than using the AddWithValue method:

command.Parameters.Add("@now", SqlDbType.DateTime2).Value = DateTime.Now;
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

this looks like the probable culprit... :)
ok I will try this as originally I was using the same code with the old datetime type which did not work either this seems logical to be the issue.
You should read (and embrace!) Can we stop using AddWithValue() already?
@marc_s - I've added an edit to the answer as you make a good point. Thanks!
1

Maybe your database field is not storing your entire DateTime.Now value, because it's not precise enough. Why don't you simply compare your dates after you've formatted them as you like?

eg: (untested):

var databaseDate = d1.ToString("MM/dd/yyyy HH:mm:ss.fff");

var tempDate = d2.ToString("MM/dd/yyyy HH:mm:ss.fff");

Assert.AreEqual(databaseDate, tempDate);

I tested: using Linq To Entities My DateTime.Now is correctly saved to my datetime2(7) and equality test return True.

Are you sure you're passing your correct datetime value to the database? without truncating it?

Improve this answer

4 Comments

according to: msdn.microsoft.com/en-us/library/bb677335.aspx the default precision is the max precision
what is the required precision to accurately store a .net datetime object?
yes sure I am passing the correct object as its being modified, its just the .Ticks value is not being reflected as the same value when being compared with the Assert
Use HH not hh. HH is 24-hour, hh is 12-hour.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.