Today I found a post at MSDN to exclude Sunday using Datediff(). I worked around the query and found a solution
declare @start datetime,
@end datetime
set @start = '2006-01-01'
set @end = '2006-01-08'
;
with calendar(date,isweekday, y, q,m,d,dw,monthname,dayname,w) as
(
select @start ,
case when datepart(dw,@start) in (1,7) then 0 else 1 end,
year(@start),
datepart(qq,@start),
datepart(mm,@start),
datepart(dd,@start),
datepart(dw,@start),
datename(month, @start),
datename(dw, @start),
datepart(wk, @start)
union all
select date + 1,
case when datepart(dw,date + 1) in (1,7) then 0 else 1 end,
year(date + 1),
datepart(qq,date + 1),
datepart(mm,date + 1),
datepart(dd,date + 1),
datepart(dw,date + 1),
datename(month, date + 1),
datename(dw, date + 1),
datepart(wk, date + 1) from calendar where date + 1<= @end
)
select * into #test from calendar option(maxrecursion 10000)
Select DATEDIFF(DD,@start,@end)+1-Count(case when #test.isweekday=0 then #test.isweekday end )
from #test where #test.date between @start and @end
You can also find the solution in this way
SELECT startDate,EndDate,DATEDIFF(DD,startDate,EndDate) FROM Production.WorkOrder WHERE ((DATEPART(dw, StartDate) + @@DATEFIRST) % 7) NOT IN (0, 1)
You can also find the solution in this way
SELECT startDate,EndDate,DATEDIFF(DD,startDate,EndDate) FROM Production.WorkOrder WHERE ((DATEPART(dw, StartDate) + @@DATEFIRST) % 7) NOT IN (0, 1)
Hi Sir,
ReplyDeleteI have found this post very helpful. It has given me a good clue to start with my calendar report.
Many thanks.