C#中DateTimePicker值转字符串及SQL日期查询报错求助

Hey there! Let's sort out that frustrating date conversion error you're dealing with. The issue here boils down to two main problems with your current code, and fixing them will not only resolve the error but also make your query much safer.

What's causing the conversion error? #

• You're converting your DateTime values to a dd/MM/yyyy string and directly concatenating it into your SQL query. SQL Server doesn't natively recognize this format as a valid date string in many cases (it depends on server settings), which is why you're getting that "Conversion failed when converting date and/or time from character string" exception.
• On top of that, this string concatenation approach leaves you wide open to SQL injection attacks—a major security no-no.

The Fix: Use Parameterized Queries #

This is the best practice for database queries, as it avoids both format issues and security risks. Here's how to rewrite your code:

// Use parameter placeholders instead of concatenating strings
string query = @"select imId, imCustomer, imAmount, imDiscount, imTotal, imPaid, imPayType, imDate 
                 from invoiceMaster 
                 where imDate >= @StartDate and imDate <= @EndDate";

SqlDataAdapter da = new SqlDataAdapter(query, declerations.con);

// Add parameters with your actual DateTime values (no string conversion needed!)
da.SelectCommand.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = startDate.Value;
da.SelectCommand.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = endDate.Value;

DataTable dt = new DataTable();
da.Fill(dt);
dgvInvoice.DataSource = dt;

Why this works: #

• Parameterized queries pass the DateTime values directly to SQL Server as native date/time types, so there's no need to convert them to strings—eliminating the format mismatch entirely.
• Parameters are treated as pure data, not executable SQL code, so SQL injection is impossible here.

If you really need to use string formatting (not recommended): #

If for some reason you can't use parameters, you can use a date format that SQL Server recognizes universally (yyyy-MM-dd), which will avoid conversion errors:

string query = "select imId,imCustomer,imAmount,imDiscount,imTotal,imPaid,imPayType,imDate from invoiceMaster where imDate >= '" + startDate.Value.ToString("yyyy-MM-dd") + "' and imDate <= '" + endDate.Value.ToString("yyyy-MM-dd") + "'";

But again, this is not safe—always go with parameterized queries whenever possible.

内容的提问来源于stack exchange，提问作者Amna Alnajjar