@Grant Fritchey

Yes u r right!

it just need to add 'Option (maxrecursion 365)' (100 is the default limit for CTE, one can set it upto 32767 with 'maxrecursion')

DECLARE @STARTDATE datetime;

DECLARE @EntDt datetime;

set @STARTDATE = '01/01/2009';

set @EntDt = '12/31/2009';

declare @dcnt int;

;with DateList as

(

select @STARTDATE DateValue

union all

select DateValue + 1 from DateList

where DateValue + 1 < convert(VARCHAR(15),@EntDt,101)

)

select count(*) as DayCnt from (

select DateValue,DATENAME(WEEKDAY, DateValue ) as WEEKDAY from DateList

where DATENAME(WEEKDAY, DateValue ) not IN ( 'Saturday','Sunday' )

)a

option (maxrecursion 365);