Sql-server – can I convert an integer to base-2 without a function

sql serversql-server-2008ssrst-sql

Here's a bit of a challenge. I am working with the table ReportServer.Schedule and there is a column called DaysOfWeek. I may not be using the proper terminology here, but this is an integer from which one can derive the days of the week an SSRS subscription is set up to run. Each day is assigned a number as follows:

  • Sunday: 1
  • Monday: 2
  • Tuesday: 4
  • Wednesday: 8
  • Thursday: 16
  • Friday: 32
  • Saturday: 64

The sum of the days the subscription is set to run is the number in this column. Thus subscriptions that run Monday through Friday have a value in this column of 62.

The end result I want is a view that derives T/F flags for each day based on this number so that I have a column for each day. The method I am currently exploring to get me there is converting this integer to base-2 so then I can convert that to a varchar and parse out the days. In this example, the result would be 111110.

The final twist – I do not have the ability to create functions or stored procedures in this database, so my strong preference is to solve this within a SELECT statement…

(If push comes to shove, I will move the raw data and use a function in a separate database – and have found a number of those online.)

Best Answer

Unless I'm completely misunderstanding your question, then the below T-SQL should do it (please let me know if this isn't what you're looking for). It utilizes bitwise operators to pull out the bit masks for the days:

-- <TEST DATA>
create table DayTable
(
    id int identity(1, 1) not null,
    DayMask tinyint not null
)
go

insert into DayTable
values
(
    62
),
(
    12
)
-- </TEST DATA>

select
        case 
            when DayMask & 1 > 0
                then 1
            else 0
        end
    as Sunday,
        case 
            when DayMask & 2 > 0
                then 1
            else 0
        end
    as Monday,
        case 
            when DayMask & 4 > 0
                then 1
            else 0
        end
    as Tuesday,
        case 
            when DayMask & 8 > 0
                then 1
            else 0
        end
    as Wednesday,
        case 
            when DayMask & 16 > 0
                then 1
            else 0
        end
    as Thursday,
        case 
            when DayMask & 32 > 0
                then 1
            else 0
        end
    as Friday,
        case 
            when DayMask & 64 > 0
                then 1
            else 0
        end
    as Saturday
from DayTable
Related Question