Posted by: Karlo Bartels | 2009/04/29

DayOfWeek bitmask

The other day I was working on a large data migration job for a customer. For some reason the customer had decided to store day-of-week data in a text field as a single number. The idea was to use a 7 digit number, where each digit represents a day of the week. So ‘1100001’ would mean that something would happen on Monday, Tuesday and Sunday, but not on any other day of that week. Creative, isn’t it?

While the customer was happy with this format, my normalized database wasn’t. I had to find a way to parse each digit (either True or False) into a couple of Bit fields that represent weekdays. I came up with the following UDF:

CREATE FUNCTION [dbo].[ParseDayOfWeekBitmask]
@Bitmask CHAR(7)
Monday bit, Tuesday bit, Wednesday bit, Thursday bit, Friday bit,
Saturday bit, Sunday bit
-- Validate bitmask
IF @Bitmask IS NULL
-- Must be 7 characters long
OR LEN(@Bitmask) < 7
-- Should only contain zeros and ones
OR LEN(REPLACE(REPLACE(@Bitmask, '0', ''), '1', '')) > 0
           INSERT INTO
SELECT 0, 0, 0, 0, 0, 0, 0;    

   -- Perform an OR operation on each digit 
Monday = (SUBSTRING(@Bitmask, 1, 1) | 0),
Tuesday = (SUBSTRING(@Bitmask, 2, 1) | 0),
Wednesday = (SUBSTRING(@Bitmask, 3, 1) | 0),
Thursday = (SUBSTRING(@Bitmask, 4, 1) | 0),
Friday = (SUBSTRING(@Bitmask, 5, 1) | 0),
Saturday = (SUBSTRING(@Bitmask, 6, 1) | 0),
Sunday = (SUBSTRING(@Bitmask, 7, 1) | 0)  


The function starts by checking whether the passed value is actually a seven digit number and if it contains ones and zeros. If it doesn’t, it returns a table with each weekday set to False.

If everything checks out OK, it performs an OR operation on each digit to retrieve its Boolean representation. Parsing the ‘1100001’ bitmask returns the following results:



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: