SQL Query to find starting and ending date of every week in a month

DECLARE @iloop int
DECLARE @Tot_Weeks Int
DROP TABLE #Weeks
CREATE TABLE #Weeks(Weekno int identity(1,1),[Start Of Week] date, [End Of Week] date )
---Input the date Here
declare @dt date = cast('2013-06-01' as date);
declare @dtstart date =  DATEADD(day, -DATEPART(day, @dt) + 1, @dt);
declare @dtend date = dateadd(DAY, -1, DATEADD(MONTH, 1, @dtstart));
--Find the Total Number of Weeks
SELECT @Tot_Weeks = DATEDIFF (week, DATEADD (m, DATEDIFF (m, 0, @dtend), 0), @dtend) + 1
Set @iloop = 0
WHILE (@iloop < @Tot_Weeks)
BEGIn
PRINT @iloop
If(@iloop = 0)
Begin
INSERT INTO #Weeks
SELECT @dtstart as [Start Of Week] , Cast( DATEADD(s,-1,DATEADD(WK, DATEDIFF(WK,0,CAST(@dtstart AS DATE))+1,0))-1 as DATE) as [End Of Week]
End
ELSE IF(@iloop = (@Tot_Weeks-1))
BEGIN
INSERT INTO #Weeks
SELECT  Cast( DATEADD(WK, DATEDIFF(WK,0,CAST(@dtstart AS DATE)),-1) as Date) as [Start Of Week] ,  @dtend as [End Of Week]
END
Else
Begin
INSERT INTO #Weeks
SELECT  Cast(DATEADD(WK, DATEDIFF(WK,0,CAST(@dtstart AS DATE)),-1) as Date) as [Start Of Week] , Cast( DATEADD(s,-1,DATEADD(WK, DATEDIFF(WK,0,CAST(@dtstart AS DATE))+1,0))-1 as DATE) as [End Of Week]
End
Set @dtstart = DATEADD(DD,7,CAST(@dtstart AS DATE))
Print @dtstart
SET @iloop = @iloop + 1
END

Select * from #Weeks

CodeProject