Date Conversion fails in C# via Parameter adding (SqlDbType.DateTime)

Question

I am new to C#. I have this statement that fails:

dbCmd.Parameters.Add("@createdTime", SqlDbType.DateTime);

The value in the createdTime is: 1/18/2012 11:54:11 AM After a lot of troubleshooting I was able to narrow down the issue. It will work IF I change the value to: 2012-01-18 11:54:01 AM. So it doesn't work with the format. I found the issue but how can I fix this issue? I mean, the DateTime format is what it is.

Here is more of my code to explain:

        dbCmd.CommandType = CommandType.Text;
        dbCmd.CommandText = sqlCmd;
        
        dbCmd.Parameters.Add("@ID", SqlDbType.Int);
        dbCmd.Parameters.Add("@FullName", SqlDbType.NVarChar);
        dbCmd.Parameters.Add("@createdTime", SqlDbType.DateTime);

Answer 1

Parse the value explicitly in C# so the db parameter contains a DateTime, not a string:

dbCmd.Parameters.Add("@createdTime", SqlDbType.DateTime)
  .Value = DateTime.ParseExact(theVariableHoldingStringDateTime, "M/d/yyyy hh:mm:ss tt", CultureInfo.InvariantCulture);

If your use of Add there is just setting up a parameter that you're later giving a value to in a loop:

for each line in text file parser blah blah

  dbCmd.Parameters["@createdTime"]
    .Value = DateTime.ParseExact(theVariableHoldingStringDateTime, "M/d/yyyy hh:mm:ss tt", CultureInfo.InvariantCulture);