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
