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
– vikas, Commented Oct 1, 2013 at 11:48
Use datetime2 if you are using sql server 2008 or plus Also check stackoverflow.com/questions/7823966/… — Kamran Shahid
– Kamran Shahid, Commented Oct 1, 2013 at 11:48

Community · Accepted Answer · 2017-05-23 12:21:37Z

6

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:

edited May 23, 2017 at 12:21

CommunityBot

11 silver badge

answered Oct 1, 2013 at 11:50

Szymon

43k16 gold badges99 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Cory Nelson · Accepted Answer · 2013-10-01 15:52:03Z

5

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).

edited Oct 1, 2013 at 15:52

answered Oct 1, 2013 at 11:49

Cory Nelson

30.3k5 gold badges77 silver badges114 bronze badges

Comments

athabaska · Accepted Answer · 2013-10-01 11:48:57Z

1

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

answered Oct 1, 2013 at 11:48

athabaska

4553 silver badges23 bronze badges

Comments

7alhashmi · Accepted Answer · 2013-10-01 11:48:56Z

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 1, 2013 at 11:48

7alhashmi

9247 silver badges26 bronze badges

Comments

vikas · Accepted Answer · 2013-10-01 12:00:27Z

0

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."

answered Oct 1, 2013 at 12:00

vikas

2,8224 gold badges29 silver badges37 bronze badges

Collectives™ on Stack Overflow

DateTime inserted in SqlServer changes value

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related