I wrote a numbers function some time ago that is designed for exactly this type of thing.

IF OBJECT_ID('dbo.GetNumsAB','IF') IS NOT NULL

DROP FUNCTION dbo.GetNumsAB;

GO

CREATE FUNCTION dbo.GetNumsAB(@low int, @high int, @range int)

RETURNS TABLE WITH SCHEMABINDING AS

RETURN

WITH

L1(N) AS (SELECT 1 FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL)) t(N)), --5

L4(N) AS (SELECT 1 FROM L1 a CROSS APPLY L1 b CROSS APPLY L1 c CROSS APPLY L1 d), --625

iTally AS

(

SELECT N = CHECKSUM(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)))

FROM L4 a CROSS APPLY L4 b CROSS APPLY L4 c

) --244,140,625

SELECT TOP (ABS((@high-@low)/ISNULL(NULLIF(@range,0),1)+1))

rn = N,

n1 = ((N-1)*@range+@low),

n2 = (( N )*@range+@low)

FROM iTally

WHERE @high >= @low;

GO

-- Examples:

SELECT * FROM dbo.GetNumsAB(1,5,1);

SELECT * FROM dbo.GetNumsAB(0,10,2);

SELECT * FROM dbo.GetNumsAB(0,1000,250);

It's similar to Itzik Ben-Gan's getnums function that dohsan posted but allows you to generate a range of numbers. It also does not blow up if @low > @high. N1 in my function is the same as N in Ben-Gan's. RN is the same as ROW_NUMBER() in that it always starts at 1...n where n is the last row.

Either way this solution or dohsan's solution will blow the doors off the recursive CTE solution above and posted on MSDN.

Using getnumsAB you can do this:

DECLARE

@FromDate Date = '7/1/2014',

@ToDate Date = '7/31/2015';

SELECT

WeekNbr = rn,

StartDay = DATEADD(DAY,n1,@FromDate),

EndDay = DATEADD(DAY,n2-1,@FromDate)

FROM dbo.GetNumsAB(0,DATEDIFF(DAY,@FromDate,@ToDate),7);

To start on Sunday you could update the @StartDate Variable like so:

SELECT @FromDate = DATEADD(DAY,(DATEPART(WEEKDAY,@FromDate)-1)*-1,@FromDate);