Home > DeveloperSection > Forums > Select all records between two dates using calendar
marcel ethan
marcel ethan

Total Post:105

Points:735
Posted on    January-27-2014 2:50 AM

 C# C# 
Ratings:


 1 Reply(s)
 876  View(s)
Rate this:

I would like to search record by two dates, the two dates will be selected by a user via calendar. This is how I wrote the query:

public List<Staff> GetStaff(DateTime DateOne, DateTime DateTwo, string staffColourCode)

{

    List<Staff> l= new List<Staff>();

    SqlConnection conn = new connection etc...

    SqlCommand command = new SqlCommand("SELECT * FROM TableName WHERE CAST(Time AS date) BETWEEN @Time and @Time", conn);

    command.Parameters.AddWithValue("@Time", DateOne);

    conn.Open();

    SqlDataReader reader = command.ExecuteReader();

    while (reader.Read())

    {

        Staff s= new Staff();

        s.Name= reader["Name"].ToString();

        etc...

        l.Add(s);

    }

    conn.Close();

    return l;

}

In my database if i replace Time with some date then it works but how do i write a query that allows a users to select two dates and based on DateOne and DateTwo search the list of Staff who has a colour code of whatever a user selects e.g. Green, Yellow etc...



Pravesh Singh

Total Post:411

Points:2881
Posted on    January-27-2014 3:12 AM

Hi Marcel,

You need two different parameters:

SqlCommand command = new SqlCommand("SELECT * FROM TableName " +

                                    "WHERE CAST(Time AS date) BETWEEN @DateOne and @DateTwo", conn);

command.Parameters.AddWithValue("@DateOne", DateOne);

command.Parameters.AddWithValue("@DateTwo", DateTwo);


Don't want to miss updates? Please click the below button!

Follow MindStick