sql server current_timestamp vs c# datetime.now

Question

Could be different the way sql server gets the current time and the way the c# compiler gets it??

I have a trigger in a sql server table, where in each update the updateDatetime column will be updated to the current date time.

I get this current date time with current_timestamp

I have also a c# code which connects to this database and using native sql updates the table, after this the trigger will be created.

The thing is that I am running a test in NUnit where I do an update, and then I retrieve the altered row. If I compare the updatedDatetime with the current one at the beginning of the test they are different. I do this with DateTime.UtcNow

The difference it's just by milliseconds, but the funny thing is that the one from the beginning of the test happens after the one from the trigger! Which it makes no sense.

Here some code to illustrate what I am saying

                DateTime dateTimeBefore = DateTime.UtcNow;

                // Act
                bookDao.Update(book);

                // Assert
                alteredBook = bookDao.GetByKey(bookingDao);


                Console.WriteLine("dateTimeBefore: {0} - {1}", dateTimeBefore, dateTimeBefore.Millisecond);
                Console.WriteLine("UpdateDateTime: {0} - {1}", alteredBook.UpdatedDateTime, alteredBook.UpdatedDateTime.Millisecond);

                Assert.IsTrue(alteredBook.UpdatedDateTime >= dateTimeBefore);

this would be the trigger

update Book set UpdatedDatetime = current_timestamp from inserted where inserted.Id = Booking.Booking.Id

I don't know if it does in your example, but the compiler may actually change the order of commands (it could move the `DateTime dateTimeBefore = DateTime.UtcNow;` below `alteredBook = bookDao.GetByKey(bookingDao);`, for instance) — C.Evenhuis, Nov 28 '12 at 12:23
is sql server instance running on the same machine where sql server instance situated? — objectbox, Nov 28 '12 at 12:32
[I've found similar discrepancies before](http://stackoverflow.com/a/12017474/73226) even with `SYSDATETIME` and `GETDATE` — Martin Smith, Nov 28 '12 at 13:30

score 1 · Accepted Answer · edited May 23 '17 at 12:15

current_time "is derived from the operating system of the computer on which the instance of SQL Server is running", so do the datetime.now. If your unit tests runs on the machine where nunit and your sql server instance is running on another machine, result of comparison of this two values depends on the local time setting of each machine.

and, besides, even in the same machine that two values (current_timestamp of sql server datetime type and datetime.now) have different precision - Converting from DateTime to SqlDateTime is inaccurate

sql server current_timestamp vs c# datetime.now

1 Answers1