Technical Article

A Flexible Day Of Week Function

,

The "DayOfWeek" parameter included as part of the DatePart function
can be a handy tool for limiting data to specific days of the week but
it is not very friendly when the required days need to be passed as a
parameter to a stored procedure.

An alterative method can be used to provide pretty much unlimited
flexibility using an integer value and some constants.

First define your constants as such:

1 = Sunday
2 = Monday
4 = Tuesday
8 = Wednesday
16 = Thursday
32 = Friday
64 = Saturday

To get every Tuesday for the year, use the Tuesday variable.

exec sel_TestDates 4

To get get multiple days, just add the constants together.
If you wanted to just get the weekdays, add Monday through Friday
for a total of 62.

exec sel_TestDates 62

For just the weekends, add Saturday and Sunday.

exec sel_TestDates 65

You can use any combination of values for odd day groupings such as
Monday, Wednesday and Friday.

exec sel_TestDates 42

 

 

 

Create table TestTable (DateValue DateTime) 
declare @sdate datetime 
declare @edate datetime 

Set NoCount On 

set @sdate = '01/01/2010' 
set @edate = '12/31/2010' 

while @sdate <= @edate 
Begin
Insert Into TestTable ( DateValue) values (@sdate)
set @sdate = DateAdd(d,1,@sdate)
End 

GO 

IF EXISTS (SELECT name 
   FROM   sysobjects 
   WHERE  name = N'sel_TestDates' 
   AND   type = 'P')
    DROP PROCEDURE sel_TestDates
GO

CREATE PROCEDURE sel_TestDates @dow int 

As 

select  * 
from TestTable
where Power(2, DatePart(dw,DateValue) -1) & @dow > 0 

GO

Rate

4.6 (5)

You rated this post out of 5. Change rating

Share

Share

Rate

4.6 (5)

You rated this post out of 5. Change rating