System.Data.SqlClient.SqlException – Conversion failed when converting date/time from character string

The error System.Data.SqlClient.SqlException – Conversion failed when converting date/time from character string occurs when SQL Server is unable to convert a string value into a valid date or time format. This typically happens when:

1. The date or time string being passed to SQL Server is in an invalid or unrecognized format.
2. The column in the database expects a datetime or date type, but the input string does not match the expected format.

Common Causes and Solutions

1. Incorrect Date/Time Format:

• Ensure the date/time string is in a format that SQL Server can recognize, such as YYYY-MM-DD or YYYY-MM-DD HH:MI:SS.
• Example: 2023-10-25 or 2023-10-25 14:30:00. If you’re passing a date from your application, ensure it is formatted correctly before sending it to the database.

   string dateString = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

2. Regional Settings:

• SQL Server uses the default date format based on its regional settings. If your application uses a different format (e.g., DD/MM/YYYY), it may cause issues.
• Use a universal format like ISO 8601 (YYYY-MM-DDTHH:MI:SS) to avoid ambiguity. Example:

   SELECT * FROM TableName WHERE DateColumn = '2023-10-25T14:30:00';

3. Implicit Conversion:

• If you’re comparing or inserting a string directly into a datetime column, SQL Server may fail to implicitly convert it.
• Explicitly convert the string to a datetime using CAST or CONVERT. Example:

   INSERT INTO TableName (DateColumn) VALUES (CAST('2023-10-25' AS datetime));

4. Null or Empty Strings:

• If the date string is null or empty, it can cause conversion errors.
• Validate the input before passing it to SQL Server. Example in C#:

   if (string.IsNullOrEmpty(dateString))
   {
       throw new ArgumentException("Date string cannot be null or empty.");
   }

5. Database Column Type Mismatch:

• Ensure the column in the database is of type datetime, date, or datetime2, and the input string matches the expected format. Example:

   CREATE TABLE TableName (
       DateColumn datetime
   );

6. Using Parameters in Queries:

• Always use parameterized queries to avoid formatting issues and SQL injection risks. Example in C#:

   using (SqlCommand cmd = new SqlCommand("INSERT INTO TableName (DateColumn) VALUES (@Date)", connection))
   {
       cmd.Parameters.AddWithValue("@Date", DateTime.Now);
       cmd.ExecuteNonQuery();
   }

Debugging Steps

1. Check the exact value being passed to SQL Server.
2. Verify the format of the date/time string.
3. Ensure the database column type matches the input type.
4. Use SQL Server’s TRY_CONVERT or TRY_CAST functions to safely handle conversion errors. Example:

   SELECT TRY_CONVERT(datetime, 'invalid-date'); -- Returns NULL if conversion fails

By addressing these issues, you should be able to resolve the Conversion failed when converting date/time from character string error.