Using DateTime in a SqlParameter for Stored Procedure, format error
- by Matt
I'm trying to call a stored procedure (on a SQL 2005 server) from C#, .NET 2.0 using DateTime as a value to a SqlParameter. The SQL type in the stored procedure is 'datetime'.
Executing the sproc from SQL Management Studio works fine. But everytime I call it from C# I get an error about the date format.
When I run SQL Profiler to watch the calls, I then copy paste the exec call to see whats going on. These are my observations and notes about what I've attempted:
1) If I pass the DateTime in directly as a DateTime or converted to SqlDateTime, the field is surrounding by a PAIR of single quotes, such as
@Date_Of_Birth=N''1/8/2009 8:06:17 PM''
2) If I pass the DateTime in as a string, I only get the single quotes
3) Using SqlDateTime.ToSqlString() does not result in a UTC formatted datetime string (even after converting to universal time)
4) Using DateTime.ToString() does not result in a UTC formatted datetime string.
5) Manually setting the DbType for the SqlParameter to DateTime does not change the above observations.
So, my questions then, is how on earth do I get C# to pass the properly formatted time in the SqlParameter? Surely this is a common use case, why is it so difficult to get working? I can't seem to convert DateTime to a string that is SQL compatable (e.g. '2009-01-08T08:22:45')
EDIT
RE: BFree, the code to actually execute the sproc is as follows:
using (SqlCommand sprocCommand = new SqlCommand(sprocName))
{
sprocCommand.Connection = transaction.Connection;
sprocCommand.Transaction = transaction;
sprocCommand.CommandType = System.Data.CommandType.StoredProcedure;
sprocCommand.Parameters.AddRange(parameters.ToArray());
sprocCommand.ExecuteNonQuery();
}
To go into more detail about what I have tried:
parameters.Add(new SqlParameter("@Date_Of_Birth", DOB));
parameters.Add(new SqlParameter("@Date_Of_Birth", DOB.ToUniversalTime()));
parameters.Add(new SqlParameter("@Date_Of_Birth", DOB.ToUniversalTime().ToString()));
SqlParameter param = new SqlParameter("@Date_Of_Birth", System.Data.SqlDbType.DateTime);
param.Value = DOB.ToUniversalTime();
parameters.Add(param);
SqlParameter param = new SqlParameter("@Date_Of_Birth", SqlDbType.DateTime);
param.Value = new SqlDateTime(DOB.ToUniversalTime());
parameters.Add(param);
parameters.Add(new SqlParameter("@Date_Of_Birth", new SqlDateTime(DOB.ToUniversalTime()).ToSqlString()));
Additional EDIT
The one I thought most likely to work:
SqlParameter param = new SqlParameter("@Date_Of_Birth", System.Data.SqlDbType.DateTime);
param.Value = DOB;
Results in this value in the exec call as seen in the SQL Profiler
@Date_Of_Birth=''2009-01-08 15:08:21:813''
If I modify this to be
@Date_Of_Birth='2009-01-08T15:08:21'
It works, but it won't parse with pair of single quotes, and it wont convert to a datetime correctly with the space between the date and time and with the milliseconds on the end.
Update and Success
First and foremost, thank you everyone for the answers. I post this for the sake of completeness and accuracy on SO - because I certainly do not do it for my pride...
I had copy/pasted the code above after the request from below. I trimmed things here and there to be concise. Turns out my problem was in the code I left out, which I'm sure any one of you would have spotted in an instant. I had wrapped my sproc calls inside a transaction. Turns out that I was simply not doing transaction.Commit()!!!!! I'm ashamed to say it, but there you have it.
I still don't know what's going on with the syntax I get back from the profiler. A coworker watched with his own instance of the profiler from his computer, and it returned proper syntax. Watching the very SAME executions from my profiler showed the incorrect syntax. It acted as a red-herring, making me believe there was a query syntax problem instead of the much more simple and true answer, which was that I need to commit the transaction!
I marked an answer below as correct, and threw in some up-votes on others because they did, after all, answer the question, even if they didn't fix my specific (brain lapse) issue.
Thanks again for the help.
