Thanks for the good article, Amit. I was looking for a way this morning to find all the dates between start date and end date, and there you go, your article is here. I manage to replicate your script to find all the middle dates. Thanks..

and thanks for other contributors for the 'MAXRECURSION' option hints..

ALTER FUNCTION [dbo].[MiddleDatesToTable]

(

@StartDateDATETIME,

@EndDateDATETIME

)

RETURNS

@listTable TABLE

(

item DATETIME

)

AS

BEGIN

;WITH rep (item, nextday) AS

(

SELECTDATEADD(DD,1,@StartDate) as 'item', DATEADD(DD,2,@StartDate) as 'nextday'

WHEREDATEDIFF(DD, DATEADD(DD,1,@StartDate), @EndDate) > 0

UNION ALL

SELECT nextday as 'item', DATEADD(DD,1,nextday) as 'nextday'

FROM rep

WHEREDATEDIFF(dd,nextday, @EndDate) > 0

)

INSERT INTO @listTable

SELECT item FROM rep option (MAXRECURSION 0)

RETURN

END