How to send a hardcoded datetime value as input to stored procedure?

This may sound easy but I've tried several times yet couldn't get what I want.

I have a stored procedure that has an input which is of DATETIME type. I've made sure the stored procedure works by providing hardcoded string as such N'2012-12-31 16:45:00'.

Now I want to execute the same stored procedure with the same input value from C#. How should I specify the value of Datetime?

Restriction Updated: parameterized SQL are not allowed

Jon Skeet
people
quotationmark

(Answer written before the restriction against parameterized SQL was added.)

How should I specify the value of Datetime?

With parameterized SQL, just like other values.

using (SqlCommand command = new SqlCommand("StoredProcName", connection))
{
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add("@Foo", SqlDbType.DateTime).Value = 
        new DateTime(2012, 12, 31, 16, 45, 0);
    // Execute here
}

Don't mess around trying to find the appropriate string form. Avoid string conversions wherever you can. While I wouldn't be too averse to including hard-coded string and numeric values in straight SQL statements where they were suitably obvious, I'd definitely use parameters for any date/time values to avoid even a whiff of conversion problems. (And I'd use parameters for any string which might need to be escaped, too...)

people

See more on this question at Stackoverflow