Script to find the complete weeks within two given dates

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

 

 

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in Interview Questions, SQL Basic, SQL Server, SQL Tips and tagged , . Bookmark the permalink.

21 Responses to Script to find the complete weeks within two given dates

  1. Pingback: CABLE TV INTERNET PHONE HOME SECURITY DEALS

  2. Pingback: 바카라사이트

  3. Pingback: Crystal Meth Shop

  4. Pingback: fun88.com

  5. Pingback: stockyard market

  6. Pingback: Darknet Marketplaces

  7. Pingback: kristalqq

  8. Pingback: agen bandarqq

  9. Pingback: idrpoker

  10. Pingback: cdr qq

  11. Pingback: senangpoker

  12. Pingback: Para Elly

  13. Pingback: gate elevator replacement motors

  14. Pingback: mejaqq pkv

  15. Pingback: Arianna

  16. Pingback: hole saw

  17. Pingback: best corn hole boards

  18. Pingback: an-st.net

  19. Pingback: de sang

  20. Pingback: SEO Las Vegas

  21. Pingback: MMO News

Leave a Reply