sql - Store multiple bit values in a single table column

Question

I need to store some sort of day-of-week scheduling in database, where I can schedule a record (say it represents a task) for a day or multiple days of the week. I need to have it stored in one column so that it's easy to save and retrieve. Then everyday I select records where the schedule matches the current day of week, something like: get me all records with schedule matches day of week 7.

What came to my mind is a series of 7 bits each one represents a day of the week where 0 means not schedule, and 1 means scheduled. For example: "1111111" for a case of an everyday task, and "1000000" for a task that only runs in the first day of week.

Just to be clear, the record might be scheduled in any combination of days, which I don't know, therefore the only input in the retrieval would be the weekday to search by.

I have two questions here:

1. What's the best way to store this type of data in SQL Server 2008? In a way that allows easy querying depending of the current day of week.

2. What are better alternatives to the above approach. Please I need efficient and easy to implement solutions.

Answer 1

You could store this as a bitfield and then use boolean logic operators to retrieve the values

for example:

CREATE TABLE [dbo].[testBF](
    [field1] [varchar](max) NOT NULL,
    [field2] [varchar](max) NOT NULL,
    [bitfield] [int] NOT NULL CONSTRAINT [DF_testBF_bitfield]  DEFAULT ((0))
) ON [PRIMARY]

Then for selects:

SELECT field1, field2,
       CASE WHEN (bitfield & 1) = 1 THEN 'monday ' ELSE '' END + 
       CASE WHEN (bitfield & 2) = 2 THEN 'tuesday ' ELSE '' END +
       CASE WHEN (bitfield & 4) = 4 THEN 'wednesday ' ELSE '' END +
       CASE WHEN (bitfield & 8) = 8 THEN 'thursday ' ELSE '' END +
       CASE WHEN (bitfield & 16) = 16 THEN 'friday' ELSE '' END as [days of week]
FROM testBF

To find all days that contain tuesday flag (tuesday is the 2nd bit or 2^1 or 2)

SELECT * 
FROM aTable
WHERE (bitfield & 2) = 2

or

SELECT * 
FROM aTable
WHERE (bitfield & 2) != 0

Note, the template in the second case will work for any bit -- that is for friday (the 5th bit or 2^4 or 16) would be

SELECT * 
FROM aTable
WHERE (bitfield & 16) != 0

Finally the general case... pass in a number (1 for monday) you get

SELECT * 
FROM aTable
WHERE (bitfield & POWER(2,@inNumOfWeekday-1)) != 0

---

This seems like a lot of work to me, when you could just save it as 5 (or 7 bit fields) but that is how you could do it.

For more examples look at the gist I wrote for another question:

https://gist.github.com/1846338

and the answer:

https://stackoverflow.com/a/9302106/215752