How do I get week dates between specified date range in SQL

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

I want week dates between specified date range inclusive date range in SQL. Like

If, @Start = '2014-05-01 00:00:00.000' and @End = '2014-05-31 00:00:00.000' Dates are this

CSS

1   2014-05-01 00:00:00.000 2014-05-07 00:00:00.000
2   2014-05-07 00:00:00.000 2014-05-14 00:00:00.000
3   2014-05-14 00:00:00.000 2014-05-21 00:00:00.000
4   2014-05-21 00:00:00.000 2014-05-28 00:00:00.000
5   2014-05-28 00:00:00.000 2014-05-31 00:00:00.000

If, @Start = '2014-05-01 00:00:00.000' and @End = '2014-05-1800:00:00.000' Dates are this

CSS

1   2014-05-01 00:00:00.000 2014-05-07 00:00:00.000
2   2014-05-07 00:00:00.000 2014-05-14 00:00:00.000
3   2014-05-14 00:00:00.000 2014-05-18 00:00:00.000

Thanks in Advance

Posted 11-Jun-14 0:41am

chetna2810

Updated 11-Jun-14 2:01am

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**Nirav Prabtani** · Answer 1 · 2014-06-11T00:52:00

Solution 1

try this.. :)

SQL

declare @fromDate datetime,@toDate datetime
set @fromDate='12 Jun 2014'
set @toDate='28 Jun 2014'


 ;WITH CTEQuery AS (
			  SELECT CAST(@fromDate AS DATETIME) AS FirstDay, DATEADD(dd, 7, @fromDate) as LastDay
			  UNION ALL
			  SELECT DATEADD(dd, 7, FirstDay), DATEADD(dd, 7, LastDay)
			   FROM CTEQuery s
			   WHERE DATEADD(dd, 7, FirstDay) <= CAST(@toDate AS DATETIME)
			   )
select * from CTEQuery

you can pass @fromDate and @toDate dynamically here i have passed it for example.. :)

Posted 11-Jun-14 0:52am

Nirav Prabtani

Updated 11-Jun-14 19:57pm

v3

Comments

chetna2810 11-Jun-14 7:01am

Sir,
It is showing dates of may month in result. I want record starting from @fromDate and ending on @toDate.
Actually i want to extract records from the table week wise thats y dates should be specific.

Nirav Prabtani 11-Jun-14 7:04am

ok wait i'll looj into it.

chetna2810 11-Jun-14 7:07am

thnx Sir..

Nirav Prabtani 11-Jun-14 7:10am

see updated solution if it doesn't work then tell me i'll give better.. :)

chetna2810 11-Jun-14 7:18am

Sir,
Your solution left dates come before @fromDate and after @toDate.
Presntly it is showing
2014-06-15 00:00:00.000 2014-06-21 00:00:00.000

But i want
2014-06-12 00:00:00.000 2014-06-18 00:00:00.000
2014-06-19 00:00:00.000 2014-06-25 00:00:00.000
2014-06-25 00:00:00.000 2014-06-26 00:00:00.000

chetna2810 11-Jun-14 8:36am

declare @WeekTable table
(
SNo int identity (1,1),
WeekStart datetime,
WeekEnd datetime,
UNIQUE NONCLUSTERED (WeekStart, WeekEnd)
)
declare @Start datetime
declare @Date1 datetime
declare @Date2 datetime
declare @Date3 datetime
declare @Date4 datetime
declare @End datetime
declare @WeekEndTable datetime
select @Start = '2014-05-01 00:00:00.000'
select @End = '2014-05-31 00:00:00.000'
select @Date1 = DATEADD(dd,6, @Start)
--select @Date1 as 'Date1'

if(@Date1 < @Start)
begin
insert into @WeekTable
select @Start,@End
end
else
begin
if(@Date1 <= @End)
begin
insert into @WeekTable
select @Start,@Date1
end
else
begin
insert into @WeekTable
select @Start,@End
end
end
select @Date2 = DATEADD(dd,7 ,@Date1)
--select @Date2 as 'Date2'
select @WeekEndTable = WeekEnd from @WeekTable
while(@WeekEndTable <> @End)
begin
if(@Date2 < @Date1)
begin
insert into @WeekTable
select dateadd(dd,1,@Date1),@Start
end
else
begin
if(@Date2 <= @End)
begin
insert into @WeekTable
select dateadd(dd,1,@Date1),@Date2
end
else
begin
insert into @WeekTable
select dateadd(dd,1,@Date1),@End
end
end
break;
end

select @Date3 = DATEADD(dd,7 ,@Date2)
--select @Date3 as 'Date3'
select @WeekEndTable = WeekEnd from @WeekTable
while(@WeekEndTable <> @End)
begin
if(@Date3 < @Date2)
begin
insert into @WeekTable
select dateadd(dd,1,@Date2),@Date1
end
else
begin
if(@Date3 <= @End)
begin
insert into @WeekTable
select dateadd(dd,1,@Date2),@Date3
end
else
begin
insert into @WeekTable
select dateadd(dd,1,@Date2),@End
end
end
break;
end

select @Date4 = DATEADD(dd,7 ,@Date3)
--select @Date4 as 'Date4'
select @WeekEndTable = WeekEnd from @WeekTable
while(@WeekEndTable <> @End)
begin
if(@Date4 < @Date3)
begin
insert into @WeekTable
select dateadd(dd,1,@Date3),@Date2
end
else
begin
if(@Date4 <= @End)
begin
insert into @WeekTable
select dateadd(dd,1,@Date3),@Date4
end
else
begin
insert into @WeekTable
select dateadd(dd,1,@Date3),@End
end
end
break;
end

select @WeekEndTable = WeekEnd from @WeekTable
while(@WeekEndTable <> @End)
begin
if(@End < @Date4)
begin
insert into @WeekTable
select dateadd(dd,1,@Date4),@Date3
end
else
begin
insert into @WeekTable
select dateadd(dd,1,@Date4),@End
end
break;
end

select * from @WeekTable

I Got it Sir..
I have done it my own efforts.
Thnx for ur response sir..
But its lengthy Suggest me some short method.

Nirav Prabtani 12-Jun-14 1:58am

Hello cheetu,
try my updated solution.. :)

chetna2810 12-Jun-14 2:48am

Thnx for ur efforts sir..

I have changed the little concept. Because If, I do like this then
1 2014-05-01 00:00:00.000 2014-05-07 00:00:00.000
2 2014-05-07 00:00:00.000 2014-05-14 00:00:00.000
3 2014-05-14 00:00:00.000 2014-05-18 00:00:00.000
* Date 07 and Date 14 are repeating twice. Which will create problem when i fetch records from table.

So I Need to do like this
1 2014-05-01 00:00:00.000 2014-05-07 00:00:00.000
2 2014-05-08 00:00:00.000 2014-05-14 00:00:00.000
3 2014-05-15 00:00:00.000 2014-05-18 00:00:00.000

Thnx once again. :)