Getting Data Between Two Dates From MySql in C#

Question

I want to get data between two dates.I am using a MySql database with my C# winforms. At the time of inserting the dates I converted the date to dd-MM-yyyy and saved these dates in the database having column of type varchar. Now I want to fetch results between two dates here is my code:

string dateFrom = dtp_dfrom.Value.ToString("dd-MM-yyyy");
string dateTo = dtp_dto.Value.ToString("dd-MM-yyyy");

//MessageBox.Show(dateFrom+" "+dateTo);

conn = new MySqlConnection(myconstring);

DataTable dt = new DataTable();
MySqlDataAdapter sda = new MySqlDataAdapter("SELECT trans_date, product_type AS Item, product_quantity, amount, SUM( product_quantity ) AS Qty, SUM( amount ) AS 'Total Price' FROM main_table WHERE trans_date BETWEEN '"+dateFrom+"' AND  '"+dateTo+"' GROUP BY product_type", conn);
sda.Fill(dt);

Now the problem is I am not getting the date as required.Anybody can help me out. I am very new to dates formats. Thanks in advance.

"saved these dates in the database having column of type varchar" -- why would you do that? The DATE/DATETIME datatypes are there for a reason. Also, creating an sql statement like that is very bad. Please use parameterized queries as shown [here](http://stackoverflow.com/questions/652978/parameterized-query-for-mysql-with-c-sharp) for example. — Corak, Aug 04 '13 at 09:22
Use query Date dataypes and please, for god sake, USE PARAMETERS. @Soner Gönül is absolutely right. — Yván Ecarri, Aug 04 '13 at 10:15

score 2 · Accepted Answer · edited Jun 06 '21 at 10:52

I converted the date to dd-MM-yyyy and saved these dates in the database having column of type varchar

No offense but this is terribly wrong. There is no reason to use varchar as column type for your date values. Use DATE or DATETIME column types instead. That's what they for.

MySQL Data Types

In your case, I suggest you to use DATE column type because it supports YYYY-MM-DD format.

And you should always use parameterized queries. This kind of string manipulations are open for SQL Injection attacks.

string dateFrom = dtp_dfrom.Value.ToString("yyyy-MM-dd");
string dateTo = dtp_dto.Value.ToString("yyyy-MM-dd");   

using(MySqlConnection conn = new MySqlConnection(myconstring))
{
   DataTable dt = new DataTable();
   using(MySqlCommand cmd = new MySqlCommand("SELECT trans_date, product_type AS Item, product_quantity, amount, SUM( product_quantity ) AS Qty, SUM( amount ) AS 'Total Price' FROM main_table WHERE trans_date BETWEEN @dateFrom AND  @dateTo GROUP BY product_type"))
   {
      cmd.Parameters.AddWithValue("@dateFrom", dateFrom);
      cmd.Parameters.AddWithValue("@dateTo", dateTo);
      MySqlDataAdapter sda = new MySqlDataAdapter(cmd, conn);
      sda.Fill(dt);
   }
}

I have changed the column type to DATE as suggested and now i have the following exception : Unable to convert MySQL date/time value to System.DateTime. — ROM, Aug 04 '13 at 10:41

score 1 · Answer 2 · answered Aug 04 '13 at 08:57

You cannot do a between on a varchar column, you have to cast it to datetime/date first.

Like the following:

cast(trans_date as date) BETWEEN '"+dateFrom+"' AND  '"+dateTo+"'

Though there is no reason why you are saving a date value into a varchar column, you should modify the table and use the correct columntype so you don't need to cast.

Btw, you should use parameters instead of string manipulations.

score 0 · Answer 3 · answered Aug 04 '13 at 09:29

You might be looking for STR_TO_DATE

string dateFrom = dtp_dfrom.Value.ToString("yyyy-MM-dd HH:mm:ss");
string dateTo = dtp_dto.Value.ToString("yyyy-MM-dd HH:mm:ss");

MySqlDataAdapter sda = new MySqlDataAdapter("SELECT trans_date, product_type AS Item, product_quantity, amount, SUM( product_quantity ) AS Qty, SUM( amount ) AS 'Total Price' FROM main_table WHERE STR_TO_DATE(trans_date,''%d-%m-%Y'') BETWEEN '"+dateFrom+"' AND  '"+dateTo+"' GROUP BY product_type", conn);

score 0 · Answer 4 · answered Aug 04 '13 at 09:32

You can't use BETWEEN with varchar data, you'll have to use datetime/date as the column type.

It would also be better to use parameters with your query like so:

DataTable dt = new DataTable();
MySqlCommand cmd = new MySqlCommand("SELECT trans_date, product_type AS Item, product_quantity, amount, SUM( product_quantity ) AS Qty, SUM( amount ) AS 'Total Price' FROM main_table WHERE trans_date BETWEEN @from AND @to GROUP BY product_type", conn);

try
{
    SqlParameter param;
    param = new MySqlParameter("@from", MySqlDbType.DateTime);
    param.Value = dateFrom ;
    cmd.Parameters.Add(param);
    param = new MySqlParameter("@to", MySqlDbType.DateTime);
    param.Value = dateTo;
    cmd.Parameters.Add(param);
    MySqlDataAdapter sda = new MySqlDataAdapter(cmd);
    sda.Fill(dt);
}
finally
{
    cmd.Dispose();
    conn.Close();
    conn.Dispose();
}

score 0 · Answer 5 · answered Aug 04 '13 at 09:33

Try this, but I stored date using DateTime as data type.

 SqlConnection con = new SqlConnection("Data Source=xxx\\SQLEXPRESS;Initial Catalog=abc;Integrated Security=true;");
    con.Open();
    SqlCommand cmd = new SqlCommand("select p.POID,p.SupplierID,p.SupplierDesc, p.CreateDate,  p.PaymentDetails,p.Status,q.Quantity,q.BalQty,q.PartNo from PoToSupplierMaster p inner join PoToSupplierMasterItems q on p.POID=q.SNO where q.PartNo='" + DropDownList3.SelectedItem.Text + "' and   p.CreateDate between '" + TextBox1.Text + "' and '" + TextBox2.Text + "'", con);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataSet ds = new DataSet();
    da.Fill(ds);
    GridView2.DataSource = ds;
    GridView2.DataBind();

in my case I used gridview. but its working fine.

Getting Data Between Two Dates From MySql in C#

5 Answers5