Script to find the complete weeks within two given dates

Sometimes we need to find out the complete weeks within two given dates. Below script will help in finding the complete weeks within 2 given dates. In this script, I have used below dates as example 

Startdate=2015-04-12
Enddate=2015-06-10 

SET DATEFORMAT YMD
if (object_id('tempdb..#weekdays') is not null)
drop table #weekdays

Declare @StartDate as datetime
Declare @EndDate as datetime
Declare @Initialweekstart as datetime
Declare @weekstart as datetime
Declare @weekend as datetime
declare @day as int

set @StartDate='2015-04-12'
set @EndDate='2015-06-10'

Create table #weekdays(weekstart datetime, weekend datetime)

select @Initialweekstart=dateadd(wk, datediff(wk, 0, @StartDate), 0)


if @Initialweekstart<@StartDate
  begin
   set @weekstart= DATEADD(wk,1,DATEADD(wk,DATEDIFF(wk,0,@Initialweekstart),0))
   print  @weekstart
   set @weekend=DATEADD(dd,6,@weekstart)
 end
else
begin
set @weekstart=@Initialweekstart
set @weekend=DATEADD(dd,6,@weekstart)
end
while @weekend<=@EndDate
begin
  insert into #weekdays(weekstart,weekend)
  values (@weekstart,@weekend)
  set @weekstart= DATEADD(wk,1,DATEADD(wk,DATEDIFF(wk,0,@weekstart),0))
   print  @weekstart
   set @weekend=DATEADD(dd,6,@weekstart)
end
select @day= datepart(dw,@EndDate)
if @day=6 or @day=7
begin
insert into #weekdays(weekstart,weekend)
values (@weekstart,@weekend)
end
select CAST(weekstart AS DATE) as WeekStart, CAST(weekend AS DATE) as WeekEnd from #weekdays

This script will gives the following result:-

WeekStart        WeekEnd
2015-04-13      2015-04-19
2015-04-20      2015-04-26
2015-04-27      2015-05-03
2015-05-04      2015-05-10
2015-05-11      2015-05-17
2015-05-18      2015-05-24
2015-05-25      2015-05-31
2015-06-01      2015-06-07

   

     

Original post (opens in new tab)