DateTime inserted in SqlServer changes value

Question

When I insert these values

09/30/2013 05:04:56.599 
09/30/2013 05:04:56.599
09/30/2013 05:04:56.599
09/30/2013 05:04:57.082

in SqlServer database, the millisecond value changes in a weird way

2013-09-30 05:04:56.600 
2013-09-30 05:04:56.600 
2013-09-30 05:04:56.600 
2013-09-30 05:04:57.083

What's wrong ?

Edit: Relevant code:

        com = new SqlCommand();
        com.Connection = con;
        com.CommandText = @"INSERT INTO [AuthSourceTimings]
                                   ([FileName]
                                   ,[JobID]
                                   ,[JobCreationTime]
                                   ,[JobSendTime]
                                   ,[JobAckTime]
                                   ,[JobDoneTime])
                             VALUES
                                   (@FileName
                                   ,@JobID
                                   ,@JobCreationTime
                                   ,@JobSendTime
                                   ,@JobAckTime
                                   ,@JobDoneTime)
                            ";

        com.Parameters.AddWithValue("@FileName", fileName);
        com.Parameters.AddWithValue("@JobID", t.JobID);

        com.Parameters.AddWithValue("@JobCreationTime", t.JobCreationTime == DateTime.MinValue ? (object)DBNull.Value : (object)t.JobCreationTime);
        com.Parameters.AddWithValue("@JobSendTime", t.JobSendTime == DateTime.MinValue ? (object)DBNull.Value : (object)t.JobSendTime);
        com.Parameters.AddWithValue("@JobAckTime", t.JobAcknowledgementTime == DateTime.MinValue ? (object)DBNull.Value : (object)t.JobAcknowledgementTime);
        com.Parameters.AddWithValue("@JobDoneTime", t.JobCompletionTime == DateTime.MinValue ? (object)DBNull.Value : (object)t.JobCompletionTime);

        com.ExecuteNonQuery();

good question +1, can you post the code from where you are sending it to db? — vikas, Oct 01 '13 at 11:48
Use datetime2 if you are using sql server 2008 or plus Also check http://stackoverflow.com/questions/7823966/milliseconds-in-my-datetime-changes-when-stored-in-sql-server — Kamran Shahid, Oct 01 '13 at 11:48

score 6 · Accepted Answer · edited May 23 '17 at 12:21

Milliseconds are stored only with precision of about 1/300th of a second in datetime format so that's where the inaccuracy comes from.

You can check this answer: Why is SQL Server losing a millisecond?

To get higher precision (100 nanoseconds), you can use DATETIME2 which was introduced in SQL Server 2008. You can get more information here:

Cory Nelson · Answer 2 · 2013-10-01T15:52:03.390

You are probably using the SQL Server type DATETIME, which the documentation states:

Rounded to increments of .000, .003, or .007 seconds.

The DATETIME2 type should be used if you want full compatibility with .NET DateTime. Both of these have an accuracy of 100 nanoseconds.

There aren't many good reasons to use DATETIME anymore. A DATETIME2(7) takes up 8 bytes which is the same as a DATETIME, but has a far greater accuracy and range. A DATETIME2(3) takes up 6 bytes and still has better accuracy (3 decimals without the weird rounding behavior detailed above).

score 1 · Answer 3 · answered Oct 01 '13 at 11:48

1

Its alll about precision. If you are using datetime type in database, try datetime2 type.

answered Oct 01 '13 at 11:48

athabaska

455
3
22

score 0 · Answer 4 · answered Oct 01 '13 at 11:48

0

You should check your table's structure. In the create table part, it should be like this:

CREATE TABLE t1 (dt DATETIME(6) NOT NULL);

To get the Output as it is.

answered Oct 01 '13 at 11:48

7alhashmi

924
7
24

score 0 · Answer 5 · answered Oct 01 '13 at 12:00

Pretty Clear that it is not the .NET or code issue

DECLARE @TestDateFrom  DATETIME='2010-06-29 05:04:56.599'
SELECT @TestDateFrom

Result

2010-06-29 05:04:56.600

And Satisfied with Szymon answer "Milliseconds are stored only with precision of about 1/300th of a second in datetime format so that's where the inaccuracy comes from."

DateTime inserted in SqlServer changes value

5 Answers5