Filter data from DB with datetimepicker

Question

I have two columns with date_of_delivery and date_of_receipt. I want to filter my data

private void button25_Click(object sender, EventArgs e)
    {
        DataSet ds = new DataSet();
        if(radioButton9.Checked)
        {
            if ((Convert.ToDateTime(dateTimePicker3.Value)) <= (Convert.ToDateTime(dateTimePicker4.Value)))
            {
                try
                {
                    string query = "SELECT work_id, surname, first_name, patronymic, type_of_service.name_type_of_service, date_of_receipt, date_of_delivery, car_model.name_model, price_for_work FROM mechanic INNER JOIN work ON work.mechanic_id = mechanic.mechanic_id INNER JOIN type_of_service ON work.type_of_service_id = type_of_service.type_of_service_id INNER JOIN car ON work.car_id = car.car_id INNER JOIN car_model ON car.car_model_id = car_model.car_model_id WHERE work.date_of_receipt >= '" + Convert.ToDateTime(dateTimePicker3.Value) + "' AND  work.date_of_delivery <= '" + Convert.ToDateTime(dateTimePicker4.Value) + "'";
                    MessageBox.Show("" + query);
                    dataGridView2.DataSource = query;
                    SqlDataAdapter da = new SqlDataAdapter(query, SqlConn);
                    da.Fill(ds, query);
                    dataGridView2.DataSource = ds.Tables[query];
                }
                catch (Exception e2)
                {
                    MessageBox.Show(e2.Message);
                }
            }
            else
            {
                MessageBox.Show("Дата начала ремонта не может быть позже его завершения ");
            }
        }
        else if(radioButton10.Checked)
        {

            string query = "SELECT work_id, surname, first_name, patronymic, type_of_service.name_type_of_service, date_of_receipt, date_of_delivery, car_model.name_model, price_for_work FROM mechanic INNER JOIN work ON work.mechanic_id = mechanic.mechanic_id INNER JOIN type_of_service ON work.type_of_service_id = type_of_service.type_of_service_id INNER JOIN car ON work.car_id = car.car_id INNER JOIN car_model ON car.car_model_id = car_model.car_model_id WHERE work.price_for_work BETWEEN " + Convert.ToInt32(textBox16.Text) + " AND " + Convert.ToInt32(textBox17.Text) + "";
            MessageBox.Show("" + query);
            dataGridView2.DataSource = query;
            SqlDataAdapter da = new SqlDataAdapter(query, SqlConn);
            da.Fill(ds, query);
            dataGridView2.DataSource = ds.Tables[query];
        }

    }

However, the data is not sorted. Because the database format of the date 01.02.2015 . How to make sure everything works

Answer 1

System.DateTime dt16 = System.DateTime.Parse(textBox16.Text);

string sTextBox16 = dt16.ToString("dd.MM.yyyy");

System.DateTime dt17 = System.DateTime.Parse(textBox17.Text);

string sTextBox17 = dt17.ToString("dd.MM.yyyy");

string query = "SELECT Required Columns WHERE work.date_of_receipt >= "+sTextBox16 +"' AND work.date_of_delivery <= '" + sTextBox17 +"'";

Answer 2

As I wrote in the comments, date types does not have a format.

You are sending a string that represents a date value to the database, (the default .ToString() of the DateTime object is called since there is an implicit conversion from date to string when you are concatenating the DateTime to the sql string).

When using strings for a date value in sql it's best to use ANSI-SQL format which is yyyy-MM-dd. This format guarantees that SQL Server will interpret the string as a proper date.

However, concatenating strings to create an SQL statement is a security hazard, since it's an opening for SQL injection attacks.

The proper way is to use parameterized queries or stored procedures.
Replace your query's where clause from this

   WHERE work.date_of_receipt >= '" + Convert.ToDateTime(dateTimePicker3.Value) + 
"' AND  work.date_of_delivery <= '" + Convert.ToDateTime(dateTimePicker4.Value) + "'"

to this:

WHERE work.date_of_receipt >= @date_of_receipt 
AND  work.date_of_delivery <= @date_of_delivery

Then use the SqlDataAdapter's SelectCommand's Parameters collection to add the values for the parameters:

SqlDataAdapter da = new SqlDataAdapter(query, SqlConn);
da.SelectCommand.Parameters.Add("@date_of_receipt ", SqlDbType.Date).Value = dateTimePicker3.Value;
da.SelectCommand.Parameters.Add("@date_of_delivery", SqlDbType.Date).Value = dateTimePicker4.Value;

(Note that the add command returns a reference to the SqlParameter you've just added, therefor you can write the .Value to specify the value of the parameter when adding it to the SelectCommand.

Note that the value of the DateTimePicker is already a DateTime type, so there is no need to use Convert.ToDateTime when adding it.

Do the same thing with all other queries (of course, don't forget to use the proper data types for the parameters).