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

Question

I have the fallowing SQL Query, that fails when getting date values:

 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();
    }

I tried converting the default values from the CalendarExtender with the following code, but it doesn't work:

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));

If I do the following query instead of the one above, and getting the values directly it works:

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

Any ideas of what could cause the error? Thanks a lot for the help!

Answer 1

You don't need to use single quotes for your parameters. With single quotes, your database manager see them as a string literal not a parameter.

Just remove them. If everything is okey other than that, it will work.

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

A few things more;

• Use using statement to dispose your SqlConnection, SqlCommand and SqlDataAdapter objects automatically instead of calling Close or Dispose methods manually.
• Do not use AddWithValue method. It may generate unexpected and surprising results sometimes. Use Add method overloads to specify your parameter types and it's size.