Resolved help creating a query for finding a record between two dates.

[sock1992]

EDIT: i fixed it. the issue was that i was inputting the incorrect format. when writing dateTimePicker1.Value.toString(dd//MM//yyyy)....it should have been **dateTimePicker1.Value.toString(MM/dd/yyyy)**


im creating a project in visual studio using the baked in SQL server. I'm trying to create a query which allows the customer to find a booking that lies between two dates. Below is my code.
The dateOfDeparture column name i have set as the "date" data type.

I'm getting the following error message when i search the query:

I know this is probably something simple, but i just can figure out what it is. ive also tried entering "dateTimePicker1.Value.toString(dd//MM//yyyy)" but doesnt make a difference.

  public void displayDataInGrid()
        {
            try
            {
                string query = (" SELECT * FROM coachSchedule WHERE dateOfDeparture BETWEEN ' " + dateTimePicker1.Value + " ' AND ' " + dateTimePicker2.Value+ " '  ");
                DataTable dt = new DataTable();
                connection.Open();
                SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
                adapter.Fill(dt);
                datagridview1.DataSource = dt;
                connection.Close();
            }
            catch (Exception message)
            {
                MessageBox.Show(message.Message);
            }
        }

 

[Skydiver]

If you used parameterized queries instead of building up the query string yourself, then the date format would not have been relevant.