Script to find the complete weeks within two given dates

Interview Questions SQL Basic SQL Server SQL Tips

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 exampleStartdate=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

 

 

21 thoughts on “Script to find the complete weeks within two given dates

  1. Pingback: 바카라사이트
  2. Pingback: Crystal Meth Shop
  3. Pingback: fun88.com
  4. Pingback: stockyard market
  5. Pingback: kristalqq
  6. Pingback: agen bandarqq
  7. Pingback: idrpoker
  8. Pingback: cdr qq
  9. Pingback: senangpoker
  10. Pingback: Para Elly
  11. Pingback: mejaqq pkv
  12. Pingback: Arianna
  13. Pingback: hole saw
  14. Pingback: an-st.net
  15. Pingback: de sang
  16. Pingback: SEO Las Vegas
  17. Pingback: MMO News

Leave a Reply