I have a table that contains an id field along with 5 fields, one for each week-day from Monday to Friday, where datatype is bit.
The table looks something like:
+---+--------+---------+-----------+----------+--------+
|id | Monday | Tuesday | Wednesday | Thursday | Friday |
+---+--------+---------+-----------+----------+--------+
| 1 | 1 | 0 | 0 | 0 | 0 |
+---+--------+---------+-----------+----------+--------+
| 2 | 1 | 0 | 0 | 0 | 0 |
+---+--------+---------+-----------+----------+--------+
| 3 | 0 | 开发者_开发问答 0 | 0 | 1 | 0 |
+---+--------+---------+-----------+----------+--------+
| 4 | 1 | 0 | 0 | 0 | 1 |
+---+--------+---------+-----------+----------+--------+
Depending on the day I'm trying to return the rows that have the bit set to true for that day. I was thinking of doing this with a where clause but can't get it to work.
I think there is something wrong in my logic, any help is greatly appreciated!
DECLARE @tDay as INTEGER
SET @tDay = datepart(weekday, getdate())
SELECT id, monday, tuesday, wednesday, thursday, friday
FROM Days
WHERE CASE WHEN @tDay = 2 then @tDay
Else Days.monday
End = 1
AND
CASE WHEN @tDay = 3 then @tDay
Else Days.tuesday
End = 1
This will work if SET DATEFIRST is 7. You need to adjust if you have some other day as first day of the week.
DECLARE @tDay as INTEGER
SET @tDay = datepart(weekday, getdate())
select id, monday, tuesday, wednesday, thursday, friday
from Days
where case @tDay
when 2 then monday
when 3 then tuesday
when 4 then wednesday
when 5 then thursday
when 6 then friday
end = 1
SELECT id, monday, tuesday, wednesday, thursday, friday
FROM Days
WHERE
CASE @tDay
WHEN 2 THEN monday
WHEN 3 THEN tuesday
WHEN 4 THEN wednesday
WHEN 5 THEN thursday
WHEN 6 THEN friday
ELSE NULL
END = 1
精彩评论