WeeklyTrigger.DaysOfWeek<\/code>, where the following coefficients are packed together into a single value:<\/p>\n 1 = Sunday\n 2 = Monday\n 4 = Tuesday\n 8 = Wednesday\n 16 = Thursday\n 32 = Friday\n 64 = Saturday<\/pre>\nFor example, 62<\/code> would be derived from 2 + 4 + 8 + 16 + 32<\/code>, which means the schedule is for every weekday (excluding Saturday and Sunday), while 65<\/code> would come from 64 + 1<\/code>, meaning only<\/em> Saturday and Sunday. You can visualize this as follows, with a couple more examples:<\/p>\n![\"Examples](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/06\/bitwise-vis-examples.png\")
<\/p>\n
You can read more about this specific enum as it relates to Task Scheduler here<\/a> and here<\/a>.<\/p>\nI’m not a big fan of “hiding” values in a single integer and requiring bitwise operations to extract them. It seems an optimization holdover from the days of punch-cards and floppy disks, where space was way<\/em> more valuable than logic and self-documentation. I’ve written about it before in When the DRY principle doesn’t apply : BITWISE operations in SQL Server<\/a>. Still, in 2024, we can’t escape it. I don’t have to deal with bitwise in SQL Server very often, but I recently came across…<\/p>\nThis use case<\/h4>\n
To interface between Task Scheduler and SQL Server, I wanted a way to translate the enum to produce different output depending on the consumer. When I know the trigger value from Task Scheduler but need to expose it to something in (or from) SQL Server, I wanted a table-valued function to return the day(s) of the week given any specific enum value. I might want one of the following output styles, say, from an enum value of 3 (which means Sunday and Monday):<\/p>\n
![\"\"](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/07\/Screenshot-2024-07-26-145804.png\")
<\/p>\n
\n\n\n\n
\u00a0<\/div>\nThe first thing I had to do was define the enum in T-SQL somehow. One way is a table value constructor<\/a>:<\/p>\n SELECT enum, dayname, dayofweek FROM \n (\n VALUES (1, 'Sunday', 1),\n (2, 'Monday', 2),\n (4, 'Tuesday', 3),\n (8, 'Wednesday', 4),\n (16, 'Thursday', 5),\n (32, 'Friday', 6),\n (64, 'Saturday', 7)\n ) AS denum (enum, dayname, dayofweek);\n<\/pre>\nThe output of this in isolation should not be shocking:<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/08\/enum-output-a.png\")
<\/p>\n
If I know the DaysOfWeek<\/code> value, I can pull the relevant values from this constructor by simply AND<\/code>ing the coefficient with the value passed in:<\/p>\n DECLARE @Enum tinyint = 42;\n\n SELECT dayname FROM \n (\n VALUES (1, 'Sunday', 1),\n (2, 'Monday', 2),\n (4, 'Tuesday', 3),\n (8, 'Wednesday', 4),\n (16, 'Thursday', 5),\n (32, 'Friday', 6),\n (64, 'Saturday', 7)\n ) AS denum (enum, dayname, dayofweek)\n WHERE enum & @Enum > 0;<\/pre>\nOutput:<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/06\/bitwise-second-output.png\")
<\/p>\n
Now I know how I can put this in a function:<\/p>\n
CREATE OR ALTER FUNCTION dbo.GetDaysOfWeekFromEnum\n (\n @Enum tinyint\n )\n RETURNS TABLE\n AS\n RETURN (\n WITH cte AS\n (\n SELECT enum, dayname, dayofweek FROM \n (\n VALUES (1, 'Sunday', 1),\n (2, 'Monday', 2),\n (4, 'Tuesday', 3),\n (8, 'Wednesday', 4),\n (16, 'Thursday', 5),\n (32, 'Friday', 6),\n (64, 'Saturday', 7)\n ) AS denum (enum, dayname, dayofweek)\n WHERE enum & @Enum > 0\n )\n SELECT dayname FROM cte\n );<\/pre>\nIf I call it with various arguments:<\/p>\n
SELECT dayname FROM dbo.GetDaysOfWeekFromEnum(62);\n SELECT dayname FROM dbo.GetDaysOfWeekFromEnum(65);\n SELECT dayname FROM dbo.GetDaysOfWeekFromEnum(34);\n SELECT dayname FROM dbo.GetDaysOfWeekFromEnum(42);<\/pre>\nI get the same output illustrated above:<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/06\/bitwise-third-output.png\")
<\/p>\n
This handles the “set” style of output, where each day is on its own row. In order to derive the different styles, I can add an argument and then create a union between the different possibilities:<\/p>\n
CREATE OR ALTER FUNCTION dbo.GetDaysOfWeekFromEnum\n (\n @Enum tinyint,\n @Style varchar(4) \/* set | csv | json *\/\n )\n RETURNS TABLE\n AS\n RETURN (\n WITH cte AS\n (\n SELECT enum, dayname, dayofweek FROM \n (\n VALUES (1, 'Sunday', 1),\n (2, 'Monday', 2),\n (4, 'Tuesday', 3),\n (8, 'Wednesday', 4),\n (16, 'Thursday', 5),\n (32, 'Friday', 6),\n (64, 'Saturday', 7)\n ) AS denum (enum, dayname, dayofweek)\n WHERE enum & @Enum > 0\n )\n SELECT dayname FROM\n (\n SELECT dayname \n FROM cte \n WHERE @Style = 'set'\n\n UNION ALL\n\n SELECT STRING_AGG(dayname, ',')\n WITHIN GROUP (ORDER BY dayofweek)\n FROM cte \n WHERE @Style = 'csv'\n \n UNION ALL \n \n SELECT Day FROM \n (\n SELECT dayname FROM\n (\n SELECT dayname \n FROM cte\n WHERE @Style = 'json'\n ) AS i\n ORDER BY dayofweek\n FOR JSON PATH, WITHOUT_ARRAY_WRAPPER\n ) AS o(Day)\n ) AS f WHERE dayname IS NOT NULL\n );<\/pre>\nIf I ask for the days for 62<\/code> in each style:<\/p>\n SELECT dayname FROM dbo.GetDaysOfWeekFromEnum(62, 'set');\n SELECT dayname FROM dbo.GetDaysOfWeekFromEnum(62, 'csv');\n SELECT dayname FROM dbo.GetDaysOfWeekFromEnum(62, 'json');<\/pre>\nYou can see the different output:<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/06\/bitwise-fourth-output.png\")
<\/p>\n
Now all of my PowerShell and C# can just pass the trigger value into SQL Server and not have to think about it. This works great for me, because T-SQL is where I am much more comfortable.<\/p>\n
Note that there’s not a tidy way in this function to ensure that the set<\/code> version will return the days in proper order, unless you output the dayofweek<\/code> column (and a dummy column for the other two union queries).<\/p>\nA second use case<\/h3>\n
Sometimes I need to know all the dates a job will run within a date range (say, a month). For this I wanted a different TVF that returns a list of dates, on the days of the week represented by the enum, between @StartDate<\/code> and @EndDate<\/code>. What this really means is, generate a series of dates<\/a> from the start date to the end date, and from that series, return the dates that match any of the weekdays present in the DaysOfWeek<\/code> value.<\/p>\nIn SQL Server 2022, we can use GENERATE_SERIES<\/code>*<\/sup> to create a set of days. For example, to get all the dates in December 2024:<\/p>\n DECLARE @StartDate date = '20241201',\n @EndDate date = '20241231';\n\n SELECT DATEADD(DAY, value, @StartDate)\n FROM GENERATE_SERIES(0, DATEDIFF(DAY, @StartDate, @EndDate));<\/pre>\nI’m not going to prove it with a screenshot, but that returns a set of 31 days, from December 1st through the 31st.<\/em><\/p>\nNext, to match these dates up with the days of the week present in the enum, I can extract the DATENAME<\/code> for the day of week from each date, and join on that. For example:<\/p>\n CREATE OR ALTER FUNCTION dbo.GetDatesFromEnumInRange\n ( \n @Enum tinyint,\n @StartDate date,\n @EndDate date\n )\n RETURNS TABLE\n AS \n RETURN\n (\n WITH daysofweek AS\n (\n SELECT dayname FROM \n (\n VALUES (1, 'Sunday', 1),\n (2, 'Monday', 2),\n (4, 'Tuesday', 3),\n (8, 'Wednesday', 4),\n (16, 'Thursday', 5),\n (32, 'Friday', 6),\n (64, 'Saturday', 7)\n ) AS denum (enum, dayname, dayofweek)\n WHERE enum & @Enum > 0\n ),\n days(Date) AS\n (\n SELECT DATEADD(DAY, value, @StartDate)\n FROM GENERATE_SERIES(0, DATEDIFF(DAY, @StartDate, @EndDate))\n )\n SELECT days.Date,\n daysofweek.dayname\n FROM days\n INNER JOIN daysofweek\n ON DATENAME(WEEKDAY, days.Date) = daysofweek.dayname\n );<\/pre>\n\n*<\/sup> Note<\/b>: I have to rely on either @@LANGUAGE being some form of English, or use the dayofweek value and rely on @@DATEFIRST. If you use a different language, or a different\u00a0 DATEFIRST setting, you could just construct your enum accordingly. Just remember that any user could have a different setting in their own session.<\/span><\/p>\nAlso, GENERATE_SERIES requires SQL Server 2022 or Azure SQL Database; see alternatives for earlier versions in this post<\/a>.<\/span><\/p>\n<\/div>\nIf I call that for the enum 62<\/code>, for the month of December:<\/p>\n DECLARE @Enum tinyint = 62,\n @StartDate date = '20241201',\n @EndDate date = '20241231';\n\n SELECT Date, dayname\n FROM dbo.GetDatesFromEnumInRange\n (@Enum, @StartDate, @EndDate)\n ORDER BY Date;<\/pre>\nHere is the output:<\/p>\n
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2024\/06\/bitwise-fifth-output.png\")
<\/p>\n
Future enhancements?<\/h3>\n
There are definitely some fixes for the future. One is that, now that I have two separate functions referencing it, I should be storing my enum in a table somewhere. The other is that I’d like to fix it so that I don’t have to rely on a stable DATEFIRST<\/code> setting but, thankfully, the function rarely gets invoked manually.<\/p>\n