Undesired rounding of DateTime in SQL Server

Question

I ran into something that seems odd. SQL Server appears to be rounding some DateTime values inappropriately when I save them to datetime columns. I suspect I'm missing something, but I can't spot it. I'm running this test against SQL Server 2008 using .NET 4.0. The following should illustrate the issue:

I have created a table in in SQL Server called Timestamps. It has two columns:

id - bigint, Identity, PK
timestamp - datetime

I also created a simple test that does the following:

1. Gets the current time, truncating the value to millisecond precision
2. Saved the truncated time to Timestamps
3. Retrieved the datetime` value from the DB and compared it to the original (truncated) DateTime object.

public static void RoundTest()
{
    DateTime preTruncation = DateTime.UtcNow;
    DateTime truncated = preTruncation.TruncateToMilliseconds();

    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["test"].ConnectionString))
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand(@"INSERT INTO Timestamps(timestamp) 
                                            VALUES(@savedTime); 
                                            SELECT SCOPE_IDENTITY() AS id");
        cmd.Parameters.Add(new SqlParameter("savedTime", truncated));
        cmd.Connection = conn;
        var id = cmd.ExecuteScalar();

        SqlCommand get = new SqlCommand(@"SELECT timestamp FROM Timestamps 
                                            WHERE id = @id");

        get.Parameters.Add(new SqlParameter("id", id));
        get.Connection = conn;
        DateTime retrieved = (DateTime)get.ExecuteScalar();

        if (retrieved != truncated)
        {
            Console.WriteLine("original: " + preTruncation.TimeOfDay);
            Console.WriteLine("truncated: " + truncated.TimeOfDay);
            Console.WriteLine("retrieved: " + retrieved.TimeOfDay);
            Console.WriteLine();
        }
    }
}

Although I expect the truncated value to be equivalent to the value returned back from the DB, that is not always the case. Here's some sample output:

original: 19:59:13.4049965
truncated: 19:59:13.4040000
retrieved: 19:59:13.4030000

original: 19:59:14.4989965
truncated: 19:59:14.4980000
retrieved: 19:59:14.4970000

original: 19:59:15.4749965
truncated: 19:59:15.4740000
retrieved: 19:59:15.4730000

original: 19:59:30.1549965
truncated: 19:59:30.1540000
retrieved: 19:59:30.1530000

TruncateToMilliseconds() looks like this:

public static DateTime TruncateToMilliseconds(this DateTime t)
{
    return new DateTime(t.Year, t.Month, t.Day, t.Hour, t.Minute, t.Second, t.Millisecond);
}

What gives? Is this really inappropriate rounding, or am I making a mistaken assumption here?

Answer 1

Datetime is only accurate to 3ms. Therefore it'll round to the nearest multiple of 3ms. To overcome this, look at the datetime2. Note that this is for SQL2008+ only

EDIT: it's not quite only to 3ms. It's rounded to increments of of .000, .003, or .007 seconds