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)
)
RETURNS @Result TABLE
(
  
Monday bit, Tuesday bit, Wednesday bit, Thursday bit, Friday bit,
  
Saturday bit, Sunday bit
)
AS
BEGIN
  
-- 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
      
BEGIN  
           INSERT INTO
@Result
              
SELECT 0, 0, 0, 0, 0, 0, 0;    
          
RETURN
       END

   -- Perform an OR operation on each digit 
  
INSERT INTO @Result
      
SELECT
          
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)  

   RETURN
END

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:

bitmaskresult

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

Categories

%d bloggers like this: