CODEWITHSUNDEEP
c#sqlasp.net
C. Lewis
C. Lewis

Reputation: 145

How To Filter Data in Database Based on Day of Week?

I have a column in my database that holds a string on different week days for my contact.

For Example: Contact 1 works Monday and Wednesday.

So in the Week_Day column the string for Contact 1 is "Monday, Wednesday". I am trying to only show the rows who work on the current day of the week, the code is below. I tried using the IN clause, but it's not working because I don't have a set day of the week I want to filter. HELP!

string today = DateTime.Today.ToString("MM-dd-yy");

string day = DateTime.Today.DayOfWeek.ToString();
string find = "select convert(varchar(8), start, 10) AS start, convert(varchar(8), end_date, 10) AS end_date, ContactName, DepartmentName, Hours, Phone, Week_Day from Schedule join Contacts on Schedule.ContactId = Contacts.ContactId inner join Departments on Contacts.DepartmentId = Departments.DepartmentId where @Current BETWEEN start AND end_date AND Week_Day IN (@Day)";
SqlCommand comm = new SqlCommand(find, con);
comm.Parameters.Add("@Current", SqlDbType.Date).Value = today;
comm.Parameters.Add("@Day", SqlDbType.NVarChar).Value = day;

con.Open();
comm.ExecuteNonQuery();
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = comm;
DataSet ds = new DataSet();
da.Fill(ds);
GridView3.DataSource = ds;
GridView3.DataBind();

con.Close();

Upvotes: 1

Views: 1203

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81970

Perhaps with DateName() and CharIndex()

Where CHARINDEX(DateName(WEEKDAY,GetDate()),Week_Day)>0 and...

Upvotes: 1

musefan
musefan

Reputation: 48415

Well you can get the current day of week (name) like so:

string day = DateTime.Today.ToString("dddd");

Then you can use that in your WHERE query with a LIKE. Change your query to have:

WHERE Week_Day LIKE @day

And add you param for @day to include % at start and end:

comm.Parameters.Add("@Day", SqlDbType.NVarChar).Value = "%" + day + "%";

More info on LIKE here

NOTE: As some comments have already mentioned it would be better to not store the data this way in a single string column, but I have answered based on your question as it stands.

Upvotes: 2

Related Questions