Jeff, challenge accepted.

something like this?:

create function [dbo].[fn_callist](@startdate date , @enddate date)

returns table as

-- CTE Tally table from http://www.sqlservercentral.com/blogs/never_say_never/2010/03/19/tally_2D00_table_2D00_cte/

return

WITH

t1 AS (SELECT 1 N UNION ALL SELECT 1 N),

t2 AS (SELECT 1 N FROM t1 x, t1 y),

t3 AS (SELECT 1 N FROM t2 x, t2 y),

t4 AS (SELECT 1 N FROM t3 x, t3 y),

-- t5 AS (SELECT 1 N FROM t4 x, t4 y), -- if 64K days aren't enough, you could expand to include t5.

cteTally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N FROM t4 x, t4 y)

--- from Jeff's code as above...

, cteDays AS

(

SELECT DayDate = DATEADD(dd,t.N-1,@startdate)

FROM cteTally t --works for zero and unit based Tally tables in this case

WHERE t.N BETWEEN 1 AND DATEDIFF(dd,@startdate,@enddate)

)

SELECT DayDate,

NextDay = DATEADD(dd,1,DayDate),

DayNum = DATEPART(dw,dateadd(day, @@DATEFIRST-1, DayDate)), --Doesn't care what DATEFIRST is set to.

NameOfDay = DATENAME(dw,DayDate)

FROM cteDays

;

go

select * from [dbo].[fn_callist]('2011-01-01', '2011-10-31')

using both a dynamic tally table and a dynamic calendar table...