passing date and time to SQL server using C#

Question

Can you please help me:

How do I pass String dateAndTime = "2/2/2012 5:57:00 pm" to SQL server using C#?

I was passing it as a String but obviously SQL does not like the syntax. it gives me an error:

Wrong syntax near 5:

Answer 1

Never pass dates as string, but use the proper datetime datatype. Look up commands and parameters instead of creating SQL statements in code. Google SqlCommand

EDIT: I was on my phone yesterday. Here is a better answer:

int GetNumberOfItemsAfterDate(DateTime dateInput) {
    // Create a sql statement with a parameter (@FirstDate)
    string sql = @"SELECT Count(*)
                   FROM   table
                   WHERE  table.date >= @FirstDate";

    // Create a sql connection
    // Always use the using statement for this
    using (var connection = new SqlConnection(connectionString)) {

        // Create a sql command
        // Always use the using statement for this
        using (var command = new SqlCommand(sql, connection)) {

            // Pass the dateinput argument to the command, and let
            // the .NET Framework handle the conversion properly!
            command.Parameters.Add(new SqlCommand("FirstDate", dateInput));

            // No need for calling .Close() or .Dispose() - the using
            // statements handle that for us
            return (int)command.ExecuteScalar();
        }
    }
}