March 26, 2012 at 11:58 am
we're using the following function to check whether a given datetime value falls within the given bitmask options, it all works quite well, however it's a costly function and depending on a user's query it can easily be called several hundred times with just one request
i was just curious if anyone could perhaps offer an alternative method to query this mask data without the use of a function or any other ways to make it more efficient
function dbo.IsDateTimeInRange
(@IN_Timedatetime,
@IN_MonthBitmasksmallint,
@IN_DayBitmasktinyint,
@IN_HourBitmaskint)
returns bit
as
begin
declare @monthsmallint,
@daytinyint,
@hourint
select @month = power(2, month(@IN_Time) - 1),
@day = power(2, datepart(dw, @IN_Time) - 1),
@hour = power(2, datepart(hour, @IN_Time))
declare @output bit
set @output = 0
if (@month & @IN_MonthBitmask = @month) AND
(@day & @IN_DayBitmask = @day) AND
(@hour & @IN_HourBitmask = @hour)
set @output = 1
return @output
end
example:
with the following bitmask options, any datetime would be considered in range:
month = 4095 = all 12 months' bits are set
day = 127 = all 7 months' bits are set
hour = 16777215 = all 24 hours' bits are set
if we wanted to query only on days say from monday to friday, but for all months and hours, the bitmask options for month and hour would stay the same, for day it would change to:
day = 31 = only the lower 5 bits are set
so now, today's date (03/26/2012 00:00) would be in range
however, this coming saturday (03/31/2012 00:00) would not be in range
if you have any questions or need clarification, please let me know
any feedback is greatly appreciated, thanks in advance!
March 26, 2012 at 12:05 pm
One question for now, how is this function used in the calling SQL? Can you provide an example?
March 26, 2012 at 12:09 pm
Definitely an interesting function, by I have to ask why you'd use that instead of a Calendar table.
I'm accustomed to working SQL Server by joining tables, not by doing bitmasks. The task you're doing, mapping all dates/times in certain patterns/ranges, is dead easy, fast, efficient, extensible, etc., by using a Calendar table.
For example, if you have a table of all dates this year, and a table of all hours, another of minutes/seconds (table would just be 1-60 and you'd join to it twice if you need seconds), you can easily join to that, do a simple Where clause on the dates table and times table, and bang, you have all valid ranges.
You could still use your bitmapping to query it, if you like and your code/end-users are accustomed to that, but you'd only have to calculate it once per query, not once per row.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply