ADO.NET Update command with datetime parameter issue

Question

Hi, I am trying do a database update via stored procedure using ADO.NET.

Basically i setup all the parameter and command and have the one of the parameter set like the following

DbParameter nm8 = provider.CreateParameter();
nm8.ParameterName = "@EDITDATE";
nm8.DbType = System.Data.DbType.DateTime;
nm8.Value = aObject.ADateTime;
command.Parameters.Add(nm8);

In the stored procedure, the input parameter is defined as

@EDITDATE       datetime = null,

and basically what the stored proc does is just get a record and update it with the EDITDATE passed in.

but i am getting this error

Error converting data type varchar to datetime.

and what i found was that the datetime value is passed in to the stored procedure as something like the following

2010-02-03 15:26:54.3100000

instead of

2010-02-03 15:26:54.310

and i think that's what is causing the casting error.

so my question is why ado.net convert the datetime in that format? how can I resolve the issue without passing the value in as string.

thanks a lot.

Answer 1

Certainly this throws an error in SQL Server 2005:

print cast('2010-02-03 15:26:54.3100000' as datetime)

whereas this works fine:

print cast('2010-02-03 15:26:54.31' as datetime)

But ... is aObject.ADateTime not of type DateTime? It sounds like it's being passed to the stored procedure as a string.

If it is indeed a string, I would suggest converting it to DateTime before you pass it in:

nm8.Value = DateTime.Parse(aObject.ADateTime);

Answer 2

Do you have to program against the base class or can you use SqlParameter? (assuming you are using connecting to Sql Server) Maybe you can try setting the SqlDbType property

SqlParameter sqlParam = (SqlParameter)nm8; //check if it's compatible before casting.
sqlParam.SqlDbType = SqlDbType.DateTime