Useful scripts for creating date ranges in SQL Server

When you want to find out the number of work days in a date range

     declare @WorkDays int  
     select @WorkDays = (  
                 (datediff(dd, @StartDate, @EndDate) + 1)  
                -(datediff(wk, @StartDate, @EndDate) * 2)  
                -(case when DATENAME(dw, @StartDate) = 'Sunday' then 1 else 0 end)  
                -(case when DATENAME(dw, @EndDate) = 'Saturday' then 1 else 0 end)  
               )  

When your users want a date ranges

 set nocount on  
 declare @Now datetime  
 declare @Today datetime  
 set @Now = getdate()  
 set @Today = DATEADD(day, DATEDIFF(day, 0, @Now), 0)  
 declare @day int  
 declare @week int  
 declare @dayofweek int  
 declare @dayofyear int  
 set @day = datepart(day, @Today)  
 set @week = datepart(week, @Today)  
 set @dayofweek = datepart(weekday, @Today)  
 set @dayofyear = datepart(dayofyear, @Today)  
 declare @weekbegin datetime  
 set @weekbegin = DATEADD(day, (@dayofweek-1)* -1, @Today)  
 declare @lastweekbegin datetime  
 set @lastweekbegin = DATEADD(week, -1, @weekbegin)  
 declare @lastweekend datetime  
 set @lastweekend = DATEADD(day, 6, @lastweekbegin)  
 declare @monthbegin datetime  
 set @monthbegin = DATEADD(day, (@day-1)* -1, @Today)  
 declare @lastmonthbegin datetime  
 set @lastmonthbegin = DATEADD(month, -1, @monthbegin)  
 declare @lastmonthend datetime  
 set @lastmonthend = DATEADD(day, -1, @monthbegin)  
 declare @yearbegin datetime  
 set @yearbegin = DATEADD(day, (@dayofyear-1)* -1, @Today)  
 declare @lastyearbegin datetime  
 set @lastyearbegin = DATEADD(year, -1, @yearbegin)  
 declare @lastyearend datetime  
 set @lastyearend = DATEADD(day, -1, @yearbegin)  
 create table #daterange  
 (  
   label varchar(20),  
   startdate datetime,  
   enddate datetime  
 )  
 insert into #daterange(label, startdate, enddate) values ('Current Week', @weekbegin, @Today)  
 insert into #daterange(label, startdate, enddate) values ('Last Week', @lastweekbegin, @lastweekend)  
 insert into #daterange(label, startdate, enddate) values ('Current Month', @monthbegin, @Today)  
 insert into #daterange(label, startdate, enddate) values ('Last Month', @lastmonthbegin, @lastmonthend)  
 insert into #daterange(label, startdate, enddate) values ('Current Year', @yearbegin, @Today)  
 insert into #daterange(label, startdate, enddate) values ('Last Year', @lastyearbegin, @lastyearend)  
 update #daterange set enddate = DATEADD(day, 1, enddate)  
 select * from #daterange  
 drop table #daterange