Insert DateTime with milliseconds into SQL Server

Question

I started a new ASP.NET MVC project two weeks ago. I'm using the micro ORM NPoco and I love it!

During testing I ran into a problem saving DateTime properties into SQL Server 2014 Express.

When I tried to insert the DateTime value 00:03:28.385 it saved in the database as 00:03:28.387. The database column type is time(7). I tried datetime2(7) and the result is always the same -> 00:03:28.387

Then I tried the plain System.Data.SqlClient:

var insert = "insert into Foo(time) values (@time)";
var conn = new SqlConnection(@"conntionString");
conn.Open();
var cmd = new SqlCommand(insertString.ToString(), _conn);
cmd.Parameters.AddWithValue("@Time",  DateTime.ParseExact("00:03:28.385", "HH:mm:ss.fff", CultureInfo.InvariantCulture));
cmd.ExecuteNonQuery();

The result was the same: 00:03:28.387

It would worked when insert the time as a string.

insert into Foo(time) values ('00:03:28.385')

So it’s not a problem from NPoco.

Answer 1

If you properly specify the parameters for your SqlCommand, it works just fine:

string connStr = "server=.;database=Test;Integrated security=SSPI;";
string insertQry = "INSERT INTO dbo.Foo(time) VALUES(@Time);";

using (SqlConnection conn = new SqlConnection(connStr))
using (SqlCommand insertCmd = new SqlCommand(insertQry, conn))
{
    // use proper Parameters syntax - specify SqlDbType!
    insertCmd.Parameters.Add("@time", SqlDbType.Time).Value = TimeSpan.Parse("00:03:28.385");

    conn.Open();
    insertCmd.ExecuteNonQuery();
    conn.Close();
}

I think the .AddParameterWithValue might just guess the datatype wrong and use SqlDbType.DateTime which corresponds to the DATETIME type in SQL Server - and that does have a 3.33ms accuracy - so .385 would be "rounded up" to .387 for that datatype.