SQL Query Selecting data from a specific time period

Question

So im trying to select students, rooms and so on from a specific time period which is between 01-01-2020 and 30_06-2020. I tried googleing to see if i could find the answer but it seems tricky to just find something that will work for my instance

 public static List<Student_Room> GetFirstSemesterStudents(int id)
        {
            List<Student_Room> studentRoomList = new List<Student_Room>();
            string query = $"select Leasing.Student_No, Leasing.Room_No, Student.Name, Leasing.Date_From, Leasing.Date_To from Leasing,Student where Leasing.Date_from = '01012020' AND Leasing.Date_To = '30062020' AND Dormitory_No = @id";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = new SqlCommand(query, connection);
                command.Parameters.AddWithValue("@id", id);
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Student_Room studentRoom = new Student_Room();
                        studentRoom.Student_No = Convert.ToInt32(reader["Student_No"]);
                        studentRoom.Student_Name = Convert.ToString(reader["Name"]);
                        studentRoom.Room_No = Convert.ToInt32(reader["Room_No"]);
                        studentRoom.Date_From = Convert.ToDateTime(reader["Date_From"]);
                        studentRoom.Date_To = Convert.ToDateTime(reader["Date_To"]);
                        studentRoomList.Add(studentRoom);
                    }

                    return studentRoomList;
                }
            }
        } 

The main problem is just that i dont know how to write the query, ther rest should be correct. i get the "Conversion failed when converting date and/or time from character string" when i try to run the function on the site

We just started learning about this stuff so im still not the best at queries and dont know much, thank for looking at it and helping appriciate it :D

Answer 1

make sure in database you have to define the datatype for date as the same in the Student_Room class, either date alone or datetime , if you want to chage to date alone as it is defined in the class the you can use

studentRoom.Date_From = DateTime.Parse(reader["Date_From"]);
studentRoom.Date_To = DateTime.Parse(reader["Date_To"]);