Conversion failed when converting date and/or time from character string

Datetimes. They can be the hero or your worst enemy.

This error is the bane of my existence as a programmer. This is worse than “Hi there! We just wanted to show an error for no reason. Restart Visual Studio. Love, Microsoft” …..

Every time this happens, I forget the fix so now it becomes a post I can Google and maybe save some troubleshooting time too.

If you are like I am, you do a little ADO between your C# code and SQL data. I’m very good at not casting and converting properly when inserting to SQL. That’s another story altogether.

Create your SQL table, adding multiple types (varchar, datetime, bit). Now you have the table to fill.

Create your code to talk to the SQL table you created above (conversations are better with more than 1 thing talking, right?):

public static void WriteLog (OrderDetail order)
 {
 using (SqlConnection dbConnection = new SqlConnection (ConnectionString)
 {
 dbConnection.Open ();
 
 try
 {
 SqlCommand cmdInsert = new SqlCommand (InsertLog, dbConnection);
 cmdInsert.Parameters.AddWithValue ("@custnum", order.custnum);
 cmdInsert.Parameters.AddWithValue ("@Number", order.PONumber);
 cmdInsert.Parameters.AddWithValue ("@dateval", order.processDate);
 cmdInsert.Parameters.AddWithValue ("@orderType", "EDI");
 cmdInsert.ExecuteNonQuery ();
 }
catch (Exception ex)
 {
 Console.WriteLine ("Log this error to blame on someone else: " + ex.Message);
 Mail.SendeMail (EmailToSelf, " ADO Error", "Log error says you failed miserably because: " + ex.Message);
 }
 }
 }

Now, fill your variables and boom!!

Big old error “Conversion failed when converting date and/or time from character string. “

Well, crap.

We review the values:

The values are correct – when I verified the param values directly into SQL, the insert succeeds.

Next I check my SQL connector, is the connection string correct? Yes.

Next, check your insert statement. A-ha!!

In this instance, I was rewriting a new C# application from an old VB app …..the syntax differences got me. In VB, the SQL queries use surrounding quotes in the statement ….not so in C#.

private const string insert_Write_Log = @"insert into [_Log] ([Customer_Number],[CPONumber],[DateProcessed],[Order_Type]) values ('@custnum','@PONumber','@date','@orderType')";

Should be:

private const string insert_Write_Log = @" insert into [_Log] ([Customer_Number],[CPONumber],[DateProcessed],[Order_Type]) values (@custnum,@PONumber,@dateval,@orderType)";

I spent too much time on troubleshooting this issue and I hope this helps you too!