Friday, July 24, 2009

how to get number of business days between two given dates


CREATE FUNCTION dbo.fnGetNoOfBusinessDays(@STARTDATE datetime,@EntDt datetime)
returns int
as
BEGIN
declare @dtCnt int;
;with DateList as 
 ( 
    select cast(@STARTDATE as datetime) DateValue 
    union all 
    select DateValue + 1 from    DateList    
    where   DateValue + 1 < convert(VARCHAR(15),@EntDt,101) 
 )
    select @dtCnt = count(*) from DateList where DATENAME(WEEKDAY, DateValue ) not IN ( 'Saturday','Sunday' )
return @dtCnt
END
go

select dbo.fnGetNoOfBusinessDays(getdate(),getdate()+50) as NoOfBusinessDays


No comments: