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

Question

我有以下SQL查询，当获取日期值时失败:

 String strQuery = @"SELECT TOP 100000" +
    "[dbo].[Service].[company_name] AS 'Company Name', [dbo].[Service].[status_description] AS 'Status Description'," +
    "DATEPART(month, [dbo].[Service].[date_entered]) AS 'Month', COUNT([dbo].[Service].[status_description]) AS 'Count'" +
    "FROM [dbo].[Service] WITH(NOLOCK)" +
    "WHERE  ([dbo].[Service].[status_description] = @Status)" +
    // ****  I used the following to get values from Ajax CalendarExtender 
    // **** (Ajax Format from: yyyy-MM-ddTHH:mm:ss.mmm to: yyyy-MM-ddT23:59:59.000) *******
    "AND ([dbo].[Service].[date_entered] BETWEEN '@DateFrom' AND '@DateTo')" +
    "GROUP BY [dbo].[Service].[company_name], [dbo].[Service].[status_description]," +
    "DATEPART(month, [dbo].[Service].[date_entered])" +
    "ORDER BY [dbo].[Service].[status_description] ASC;";
    System.Globalization.CultureInfo en = new System.Globalization.CultureInfo("en-US");
    SqlConnection con = new SqlConnection(strConnString);
    SqlCommand cmd = new SqlCommand(strQuery + "", con);
    cmd.Parameters.AddWithValue("@Status", DdlTickets.SelectedItem.Value);
    cmd.Parameters.AddWithValue("@DateFrom", TxtDateFrom.Text);
    cmd.Parameters.AddWithValue("@DateTo", TxtDateTo.Text);        
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    cmd.Connection = con;
    try
    {
        con.Open();
        if (DdlTickets.Items.Count > 1)
        {
            da.Fill(ds, strQuery);
            // *** the values come from Ajax, but when It gets to fill the Gridview, it fails with error ****
            GridView1.DataSource = ds.Tables[strQuery];
            GridView1.DataBind();
            BtnCSV.Visible = true;
        }
        if (GridView1.Rows.Count <= 0)
        {
            BtnCSV.Visible = false;
        }
        else
        {
            //Set Later
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        con.Close();
        con.Dispose();
    }

我尝试用以下代码转换CalendarExtender的默认值，但它不起作用:

cmd.Parameters.AddWithValue("@DateFrom", DateTime.ParseExact(TxtDateFrom.Text, "yyyy-MM-ddTHH:mm:ss.mmm", CultureInfo.InvariantCulture));
cmd.Parameters.AddWithValue("@DateTo", DateTime.ParseExact(TxtDateTo.Text, "yyyy-MM-ddTHH:mm:ss.mmm", CultureInfo.InvariantCulture));

如果我执行以下查询而不是上面的查询，并直接获取值，它可以工作:

"AND ([dbo].[Service].[date_entered] BETWEEN '2015-04-02T00:00:00.000' AND '2015-09-02T23:59:59.000')" +

知道是什么导致了这个错误吗?非常感谢你的帮助!

Answer 1

您不需要为您的参数使用单引号。使用单引号，您的数据库管理器将它们视为字符串字面值而不是参数。

把它们去掉。

AND ([dbo].[Service].[date_entered] BETWEEN @DateFrom AND @DateTo)

还有几件事;

• 使用using语句自动处理SqlConnection, SqlCommand和SqlDataAdapter对象，而不是手动调用Close或Dispose方法。
• 不使用AddWithValue方法。它有时可能产生意想不到的和令人惊讶的结果。使用Add方法重载来指定你的参数类型和它的大小。