C# and MySql, select between two date

Question

Sorry for my english, this is very bad.

private void loadDetalle()
{
    DateTime today = DateTime.Today;
    string todayS = today.ToString("yyyy/MM/dd");
    DateTime fechaComparativa = DateTime.Today.AddDays(-30);
    string fechaComparativaS = fechaComparativa.ToString("yyyy/MM/dd");
    string queryDetalle30DiasIngresos = "select SUM(monto) from finanzas.ingresos WHERE fecha between " + fechaComparativaS + "and" + todayS;
    executeQueryDetalle(labelIngreso30, queryDetalle30DiasIngresos);
}


private void executeQueryDetalle(Label Detalle, string query)
{
    try
    {
        mcon.Open();
        mcd = new MySqlCommand(query, mcon);
        object result = mcd.ExecuteScalar();
        Detalle.Text = Convert.ToString(result);
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
    finally
    {
        mcon.Close();
    }
}

And the problem with this is this:

You have a error in your SQL syntax, check the manual that corresponds to your MYSQL server version for the right syntax to use near '' at line 1

change the query to use parameters the error is telling you exactly what the issue is.. there Query is picking up `"` as a value you're passing.. this is the problem when people don't use parameterized query's properly also your `"and" should be `"and "` you need a space lastly do a google search on the following `MySQL - select data from database between two dates` — MethodMan, Feb 22 '16 at 01:04

score 0 · Answer 1 · edited May 23 '17 at 12:23

I see 3 syntax issues with your query:

Dates should be in yyyy-mm-dd format in mysql, and not in yyyy/MM/dd.
Dates should be enclosed by single quotes (') as if they were a string.
There is no space between the dates and the and operator.

So, your code should be something like below:

private void loadDetalle()
{
    DateTime today = DateTime.Today;
    string todayS = today.ToString("yyyy-MM-dd");
    DateTime fechaComparativa = DateTime.Today.AddDays(-30);
    string fechaComparativaS = fechaComparativa.ToString("yyyy-MM-dd");
    string queryDetalle30DiasIngresos = "select SUM(monto) from finanzas.ingresos WHERE fecha between '" + fechaComparativaS + "' and '" + todayS + "'";
    executeQueryDetalle(labelIngreso30, queryDetalle30DiasIngresos);
}

If you store the dates as string in the format you described inyour code, even then the 2nd and 3rd points apply.

However, the biggest issue is that you use string concatenation to create a query instead of using parametrised queries. Check out this SO topic for an example on using mysqlcommand with parameters.

C# and MySql, select between two date

1 Answers1