Monday, July 27, 2009

how to get The Business Dates between the two given dates

CREATE FUNCTION dbo.fnGetNoOfBusinessDates
(@STARTDATE datetime,@EntDt datetime)
RETURNS TABLE
AS
RETURN
 
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 * from DateList where DATENAME(WEEKDAY, DateValue ) not IN ( 'Saturday','Sunday' )
GO

select * from dbo.fnGetNoOfBusinessDates(getdate(),getdate()+30);


No comments: