Convert integer to a list of week days

Question

I have an integer stored in a database (SQLAgent Frequency Interval) This integer is actually the sum of the selected days of the week that the schedule is to run The possible values are any combination of these values

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

ex 65 means that the schedule should run on Saturday and Sunday

My problem is that I need to represent these values as the text "Saturday" and "Sunday" when given 65 and I am trying to do this in SQL

Other than a huge CASE statement with all of the possible combinations can anyone think of a way to do this?

Thanks

Answer 1

I would start by putting it on an user defined function. Also, you can use an and that operates at the bit level to check for it - I think it is &, will update.

Update 1: It was &, Jason already put up an example. I still recommend to use an user defined function for it :).

Answer 2

Edit: This is C# code to do the bit operations. I posted it before reading the question in detail, but I will leave it here as an alternative. Is the database really the best place to do this...?

You can use an array:

// input: int value
string[] weekdays = { "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Saturday" };
int flag = 1
List<string> days = new List<string>();
foreach (string day in days) {
   if ((value && flag) != 0) {
      days.Add(day);
   }
   flag <<= 1;
}

The result is a list of strings, if you want to merge them you can for example do:

string selected = String.Join(", ", days.ToArray());

Answer 3

You can use bit-wise operators in T-SQL. Here's how:

SELECT
  ( CASE WHEN daybits & 1 = 1 THEN 'Sunday ' ELSE '' END ) +
  ( CASE WHEN daybits & 2 = 2 THEN 'Monday ' ELSE '' END ) +
  ( CASE WHEN daybits & 4 = 4 THEN 'Tuesday ' ELSE '' END ) +
  ...
  ( CASE WHEN daybits & 64 = 64 THEN 'Saturday ' ELSE '' END ) +

That will produce "Sunday Saturday" for example.

Answer 4

DECLARE @in INTEGER;
SET @in = 63;
WITH series(n) AS
    (
    SELECT 0
    UNION ALL
    SELECT n + 1
    FROM series
    WHERE n < 6
    )
SELECT  CASE WHEN ROW_NUMBER() OVER (ORDER BY n) > 1 THEN ', ' ELSE '' END + DATENAME(weekday, DATEADD(day, n, '1980-09-03')) 
FROM    series
WHERE   (POWER(2, n) & @in) > 0
ORDER BY n
FOR XML PATH ('')