Multiple days of week stored in one field

Question

I've encountered a bit of a mental roadblock regarding the way a specific integer field is storing data.

Specifically, there is a column with integers that range from 1 - 127; each integer represents a combination of different days of the week. For example: Monday = 2^0 or 1, Tuesday = 2^2 or 2, Wednesday = 2^3 or 8; with the option of addition, Monday + Tuesday = 3.

I've been able to extract the date values partially using the example found here. However, that particular example does not work when two days get added together (eg. Monday + Tuesday = 3). Can anyone point me in the right direction?

FYI, I am using SQL Server 2008 R2. My apologies if this has been posted before, I took a look but was unable to find any other postings.

Answer 1

What you're dealing with is referred to as bitwise operators.

Here's a good read on it with clear simple examples.

For the sake of completeness, here is what you're looking at broken down into columns for each day of the week.

DECLARE @bitwise TABLE (someValue TINYINT)

INSERT INTO @bitwise (someValue)
SELECT 1 UNION
SELECT 5 UNION
SELECT 127

SELECT someValue, CASE WHEN (1&someValue)=1 THEN 'SUNDAY' END
                , CASE WHEN (2&someValue)=2 THEN 'MONDAY' END
                , CASE WHEN (4&someValue)=4 THEN 'TUESDAY' END
                , CASE WHEN (8&someValue)=8 THEN 'WEDNESDAY' END
                , CASE WHEN (16&someValue)=16 THEN 'THURSDAY' END
                , CASE WHEN (32&someValue)=32 THEN 'FRIDAY' END
                , CASE WHEN (64&someValue)=64 THEN 'SATURDAY' END

FROM @bitwise

Answer 2

You'll need to use bitwise operators, most likely.

SELECT *
FROM Table
WHERE DaysOfWeek & 3 = 3

Or, if it makes more sense:

SELECT *
FROM Table
WHERE DaysOfWeek & 1 = 1
    AND DaysOfWeek & 2 = 2

I strongly suggest you create a VIEW with a query like @JNevill describes.

Answer 3

It seems like you could just grab the bit you need and store the result in their own field for each day of the week.

SELECT
    cast(day_of_week & 1 as bit) AS 'Monday',
    cast(day_of_week & 2 as bit) AS 'Tuesday',
    cast(day_of_week & 4 as bit) AS 'Wednesday',
    cast(day_of_week & 8 as bit) AS 'Thursday',
    etc...