CODEWITHSUNDEEP
sql-server-2005t-sqldayofweek
WACM161
WACM161

Reputation: 1033

Convert integer to a list of week days

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

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

Upvotes: 2

Views: 3759

Answers (4)

Quassnoi
Quassnoi

Reputation: 425271

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 ('')

Upvotes: 0

Guffa
Guffa

Reputation: 700192

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());

Upvotes: 0

eglasius
eglasius

Reputation: 36027

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 :).

Upvotes: 0

Jason Cohen
Jason Cohen

Reputation: 83011

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.

Upvotes: 10

Related Questions