Using DateTime in a SqlParameter for Stored Procedure, format error

Question

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.

Answer 1

How are you setting up the SqlParameter? You should set the SqlDbType property to SqlDbType.DateTime and then pass the DateTime directly to the parameter (do NOT convert to a string, you are asking for a bunch of problems then).

---

Ok, I see what is happening now. You should be able to get the value into the DB. If not, here is a very simple example of how to do it:

static void Main(string[] args)
{
    // Create the connection.
    using (SqlConnection connection = new SqlConnection(@"Data Source=..."))
    {
        // Open the connection.
        connection.Open();

        // Create the command.
        using (SqlCommand command = new SqlCommand("xsp_Test", connection))
        {
            // Set the command type.
            command.CommandType = System.Data.CommandType.StoredProcedure;

            // Add the parameter.
            SqlParameter parameter = command.Parameters.Add("@dt", System.Data.SqlDbType.DateTime);

            // Set the value.
            parameter.Value = DateTime.Now;

            // Make the call.
            command.ExecuteNonQuery();
        }
    }
}

I think part of the issue here is that you are worried that the fact that the time is in UTC is not being conveyed to SQL Server. To that end, you shouldn't, because SQL Server doesn't know that a particular time is in a particular locale/time zone.

If you want to store the UTC value, then convert it to UTC before passing it to SQL Server (unless your server has the same time zone as the client code generating the DateTime, and even then, that's a risk, IMO). SQL Server will store this value and when you get it back, if you want to display it in local time, you have to do it yourself (which the DateTime class will easily do).

All that being said, if you perform the conversion and then pass the converted UTC date (the date that is obtained calling ToUniversalTime, not by converting to a string) to the stored procedure.

And when you get the value back, call ToLocalTime to get the time in the local time zone.

Answer 2

Can you post the C# code you are using? And the parameters of your SPROC as shown by SQL Server? Check that you are actually creating a parameter with a datetime type.

Answer 3

As others have said, just post the code. With that said, it sounds like you are trying to set your parameter equal to a string instead of to a DateTime object.

Answer 4

Just use:

 param.AddWithValue("@Date_Of_Birth",DOB);

That will take care of all your problems.

Answer 5

Here is how I add parameters:

sprocCommand.Parameters.Add(New SqlParameter("@Date_Of_Birth",Data.SqlDbType.DateTime))
sprocCommand.Parameters("@Date_Of_Birth").Value = DOB

I am assuming when you write out DOB there are no quotes.

Are you using a third-party control to get the date? I have had problems with the way the text value is generated from some of them.

Lastly, does it work if you type in the .Value attribute of the parameter without referencing DOB?

Answer 6

Perhaps you could also post the procedure iteslf, or at least the signature

Answer 7

If you use Microsoft.ApplicationBlocks.Data it'll make calling your sprocs a single line

SqlHelper.ExecuteNonQuery(ConnectionString, "SprocName", DOB)

Oh and I think CasperOne is correct...if you want to ensure the correct datetime over multiple timezones then simply convert the value to UTC before you send the value to SQL Server

SqlHelper.ExecuteNonQuery(ConnectionString, "SprocName", DOB.ToUniversalTime())