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();

Answer 1

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:

• http://msdn.microsoft.com/en-us/library/bb677335.aspx
• http://www.mssqltips.com/sqlservertip/1616/sql-server-2008-date-and-time-data-types/

Answer 2

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