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.

"@Time" in cmd.Parameters.AddWithValue starts with capital 'T' but in your string, it starts with lower case 't'. could this be the issue? — Peyman, Jun 29 '15 at 22:31
From the code and examples it looks like you are inserting time only and not date & time... — jjczopek, Jun 29 '15 at 22:31
Probably you shouldn't be using `addwithvalue` and be explicit about parameter datatype. — Martin Smith, Jun 29 '15 at 22:48
I suspect it is because the line, `cmd.Parameters.AddWithValue("@Time",`... passes a DateTime to the parameter. — jac, Jun 29 '15 at 22:50
You should check out [Can we stop using AddWithValue() already?](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) and stop using `.AddWithValue()` - it can lead to unexpected and surprising results... — marc_s, Jun 30 '15 at 04:36

score 3 · Accepted Answer · answered Jun 30 '15 at 04:42

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.

Thanks @marc_s! I just figured out that time (SQL Server) and TimeSpan (.NET) are working perfect together. Thanks! — stevo, Jun 30 '15 at 06:45

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

1

I don't know all the details, but see Otiel's answer on this page. It seems to match what you are seeing.

Milliseconds in my DateTime changes when stored in SQL Server

This is due to the precision of the SQL datetime type. According to msdn:
Datetime values are rounded to increments of .000, .003, or .007 seconds

edited Jun 20 '20 at 09:12

Community

1
1

answered Jun 29 '15 at 22:45

user1304444

1,713
3
21
38

2

They say they are using `time(7)` datatype. Though does look like it is getting converted to that somewhere. – Martin Smith Jun 29 '15 at 22:47
1

This doesn't apply because the OP is using a time data type and specifying a precision of 7. This would be correct if the OP was using the DateTime datatype: https://msdn.microsoft.com/en-us/library/bb677243(v=sql.120).aspx – jac Jun 29 '15 at 22:48
Note that so far this should be comment/vote to close as duplicate. http://meta.stackoverflow.com/questions/297804/summer-time-is-it-still-acceptable-to-close-as-duplicate – Alexei Levenkov Jun 29 '15 at 22:48
jac: I think it affects time, date, datetime2, and datetimeoffset in SQL Server: https://msdn.microsoft.com/en-us/library/ms187819.aspx – user1304444 Jun 29 '15 at 22:51
Thanks for all your answers! T – stevo Jun 30 '15 at 05:00
Thanks! I basically change the DateTime data type to TimeSpan because I’m just interest at the time with milliseconds and time (SQL Server) <-> TimeSpan (.NET) works perfect together! Thanks again! Cheers, Stefan – stevo Jun 30 '15 at 06:41

Insert DateTime with milliseconds into SQL Server

2 Answers2