1

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();
Roman Marusyk
  • 23,328
  • 24
  • 73
  • 116
C. Lewis
  • 145
  • 1
  • 6
  • 17
  • 2
    If you can, you would be **much** better to store this as a bit field, so 5 is Monday and Wednesday (Binary 101), you can they do a bit field check on bit 0 to find all people who work on Mondays i.e. `WHERE (WeekDay And 1) = 1` – Matt Wilko Aug 11 '16 at 15:54
  • 1
    It would actually be much better to use a separate Table that assigns persons to weekdays. This way, the database server can use indexes when performing a query – NineBerry Aug 11 '16 at 15:57
  • Possible duplicate of [How can I tell if a VARCHAR variable contains a substring?](http://stackoverflow.com/questions/12265411/how-can-i-tell-if-a-varchar-variable-contains-a-substring) – sr28 Aug 11 '16 at 16:00

2 Answers2

2

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.

musefan
  • 47,875
  • 21
  • 135
  • 185
1

Perhaps with DateName() and CharIndex()

Where CHARINDEX(DateName(WEEKDAY,GetDate()),Week_Day)>0 and...
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66