Multiple Days Of Week Stored In One Field

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

Solution 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@bitwiseTABLE (someValue TINYINT)

INSERTINTO@bitwise (someValue)
SELECT1UNIONSELECT5UNIONSELECT127SELECT someValue, CASEWHEN (1&someValue)=1THEN'SUNDAY'END
                , CASEWHEN (2&someValue)=2THEN'MONDAY'END
                , CASEWHEN (4&someValue)=4THEN'TUESDAY'END
                , CASEWHEN (8&someValue)=8THEN'WEDNESDAY'END
                , CASEWHEN (16&someValue)=16THEN'THURSDAY'END
                , CASEWHEN (32&someValue)=32THEN'FRIDAY'END
                , CASEWHEN (64&someValue)=64THEN'SATURDAY'ENDFROM@bitwise

Solution 2:

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 & 1as bit) AS'Monday',
    cast(day_of_week & 2as bit) AS'Tuesday',
    cast(day_of_week & 4as bit) AS'Wednesday',
    cast(day_of_week & 8as bit) AS'Thursday',
    etc...

Solution 3:

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

SELECT*FROMTableWHERE DaysOfWeek &3=3

Or, if it makes more sense:

SELECT*FROMTableWHERE DaysOfWeek &1=1AND DaysOfWeek &2=2

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