CODEWITHSUNDEEP
c#sql-serverdatetimeado.net
Jerry Bian
Jerry Bian

Reputation: 4238

ADO.NET DateTime sql parameter lose accuracy

I am trying to delete records with ADO.NET for SQL Server, of which there is a sql command with the DateTime type parameter. 

using (var sqlConnection = new SqlConnection(...))
{
    var command = sqlConnection.CreateCommand();
    command.CommandText = string.Format("DELETE FROM [{0}] WHERE UtcTimeStamp=@UtcTimeStamp", ...);
    command.Parameters.Add(new SqlParameter("@UtcTimeStamp", DbType.DateTime2) { Value = exactTime });
    sqlConnection.Open();
    command.ExecuteNonQuery();
}

When I pass the exactTime for example 2014-12-22 04:53:21.9690398, meanwhile I checked the database, the record with this UtcTimeStamp value exists, but the record would not be deleted after I execute my code.

I checked the Sql Profier, found the UtcTimeStamp parameter in sql command was parsed to 2014-12-22 04:53:21.970, I think that's why my code doesn't work.

So, how can I force the parsed UtcTimeStamp use the exactly same accuracy with the input?

Upvotes: 1

Views: 1383

Answers (2)

Jerry Bian
Jerry Bian

Reputation: 4238

Ok, I think I have found the point: the SqlParameter's second argument is not correct, it should be

new SqlParameter("@UtcTimeStamp", SqlDbType.DateTime2) { Value = exactTime }

If I pass the DbType into it, it will be identified by it's another constructor:

public SqlParameter(string parameterName, object value) : this()
{
   this.ParameterName = parameterName;
   this.Value = value;
}

So the SqlDbType property of this instance would use default SqlDbType.DateTime, which is not correct. Instead I should use this constructor:

public SqlParameter(string parameterName, SqlDbType dbType) : this()
{
   this.ParameterName = parameterName;
   this.SqlDbType = dbType;
}

Upvotes: 1

Mairaj Ahmad
Mairaj Ahmad

Reputation: 14604

Make sure when you save this vlaue in db its type is DbType.DateTime2 because this type rounds the value of nanoseconds with an accuracy of 100. So i think when you save this in database you are saving it as DbType.DateTime make sure it is DbType.DateTime2.

Upvotes: 0

Related Questions