Today, I spent more hours than I care to admit struggling to get a relatively basic section of C# code to work correctly. I tweaked it many times after searching and reading numerous online forum posts related to the specific SqlException messages I was receiving.

The two SqlException messages that I would receive (one or the other for each code execution instance, depending on the exact details of that particular code tweak):
Conversion failed when converting date and/or time from character string.
or
Failed to convert parameter value from a String to a DateTime.

I taught myself some C# skills a number of years ago and picked up a way of working with database queries in C# that worked, and never bothered to see if it was the best practice way of working with database queries. That approach was to include the query parameters within the query string, concatenating parameter values into the string as needed. Something like the following:
string sQuery = "SELECT FirstName FROM FamilyMembers WHERE LastName = '" + sLastName + "'";

Or something like this for a datetime field:
string sQuery = "SELECT FirstName, LastName FROM FamilyMembers WHERE BirthDate > '" + sDate + "'";

I have relatively recently discovered that this is not at all good coding practice, and that parameter values should rather be added via the Parameters property of the SqlCommand class. You can read up on all of the details at the linked Microsoft pages, but first I'll show you how I was attempting to use parameters.

string sQuery = "SELECT FirstName, LastName FROM FamilyMembers WHERE LastName = '@LastName' AND BirthDate > '@BirthDate';
var cmd = new SqlCommand(conn);
cmd.Parameters.Add("@LastName", SqlDbType.NVarChar, 20);
cmd.Parameters["@LastName"].Value = sLastName;
cmd.Parameters.Add("@BirthDate", SqlDBType.DateTime);
cmd.Parameters["@BirthDate"].Value = sDate;


Executing one of the SqlCommand's Execute methods would invariably result in one of the two SqlExceptions shown above. I tried everything I could think for the date/time value, including outputting it in multiple different formats and running it through DateTime.Parse or DateTime.TryParse first. Nothing I tried would work correctly.

The key detail in the code above is that I was including the referenced parameters in my query string the same way I previously included the parameter values themselves - with single quotes around them. It finally dawned on me to try the code without the single quotes around the referenced parameters, and lo and behold, the code suddenly worked!!!

Here's the updated code:
string sQuery = "SELECT FirstName, LastName FROM FamilyMembers WHERE LastName = @LastName AND BirthDate > @BirthDate;
var cmd = new SqlCommand(conn);
cmd.Parameters.Add("@LastName", SqlDbType.NVarChar, 20);
cmd.Parameters["@LastName"].Value = sLastName;
cmd.Parameters.Add("@BirthDate", SqlDBType.DateTime);
cmd.Parameters["@BirthDate"].Value = sDate;

I don't know whether this is true across all SQL query/parameter possibilities, but it's definitely true for this example. The referenced parameters do not need to be enclosed in single quotes, double quotes, square brackets, curly brackets (yes, I tried each of those!), or anything at all!!! Just reference them in the query text and let the SqlCommand object take care of incorporating the query values into the query text however they need to be incorporated!