c# SQL Server : DATETIME datatype

Question

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

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](http://stackoverflow.com/questions/30710/how-to-unit-test-an-object-with-database-queries). — hunch_hunch, Aug 26 '14 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 — Dizzle, Aug 26 '14 at 22:27
How is the DateTime2 column defined? Are you using just `datetime2` or `datetime2(n)`? — petelids, Aug 26 '14 at 22:41
I use neither and pass the whole datetime object as the parameter — Dizzle, Aug 26 '14 at 22:49

score 3 · Accepted Answer · edited May 23 '17 at 12:27

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;

edited May 23 '17 at 12:27

Community

1
1

answered Aug 26 '14 at 22:54

petelids

12,305
3
47
57

this looks like the probable culprit... :) – Vland Aug 26 '14 at 22:59
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. – Dizzle Aug 26 '14 at 22:59
You should read (and embrace!) [Can we stop using AddWithValue() already?](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) – marc_s Aug 27 '14 at 04:56
1

@marc_s - I've added an edit to the answer as you make a good point. Thanks! – petelids Aug 27 '14 at 08:12

Vland · Answer 2 · 2014-08-26T23:00:03.877

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?

edited Aug 26 '14 at 23:00

answered Aug 26 '14 at 22:35

Vland

4,151
2
32
43

according to: http://msdn.microsoft.com/en-us/library/bb677335.aspx the default precision is the max precision – Dizzle Aug 26 '14 at 22:36
what is the required precision to accurately store a .net datetime object? – Dizzle Aug 26 '14 at 22:44
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 – Dizzle Aug 26 '14 at 22:56
Use `HH` not `hh`. `HH` is 24-hour, `hh` is 12-hour. – Dai Aug 26 '14 at 22:59

c# SQL Server : DATETIME datatype

2 Answers2

Linked