splitting days into intervals

Question

0.00/5 (No votes)

See more:

Hello everyone,

I need to some sql part in my project. I want to divide days into certain intervals
I have startdate,enddate and interval as inputs. Divide the days between startdate and enddate based on interval.

for example

if

startdate        enddate        interval
04/01/2012    20/02/2012    7

the output should be like this

04/01/2012  10/01/2012
11/01/2012  17/01/2012
18/01/2012  24/01/2012
25/01/2012  31/01/2012
01/02/2012  07/02/2012
08/02/2012  14/02/2012
15/02/2012  20/02/2012

please help me........

Posted 28-Oct-13 4:34am

User 9241523

Updated 28-Oct-13 21:00pm

coded007

v3

Add a Solution

2 solutions

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)

chaau · Answer 1 · 2013-10-28T13:40:00

Thanks for the nice question. It is not very often you find it here.
You can use Common Table Expression for this:

SQL

declare @StartDate datetime = '03-Jan-2012', 
  @EndDate datetime = '20-Feb-2012', 
  @Interval int = 7

;with cte as(
  SELECT @StartDate as StartDate, DateAdd(d, @Interval - 1, @StartDate) as EndDate
  UNION ALL
  SELECT DateAdd(d, @Interval, c.StartDate) as StartDate, DateAdd(d, @Interval * 2 - 1, c.StartDate) as EndDate
  FROM cte c WHERE DateAdd(d, @Interval * 2 - 1, c.StartDate) < @EndDate
)
SELECT * FROM cte
OPTION (MAXRECURSION 0)

S. M. Ahasan Habib · Answer 2 · 2013-10-28T07:33:00

Please check the following query. I tried to fulfill your requirement. Let me know if it is not fulfil your requirement.

SQL

declare @StartDate datetime = '03-Jan-2012', @EndDate datetime = '20-Feb-2012', @Interval int = 6;
declare @Flag bit = 0;
while @StartDate < @EndDate
begin	
	if @Flag = 0
	begin
		set @Flag = 1;
		set @StartDate += 1;		
	end
	else
	begin
		set @StartDate += @Interval;
		set @Flag = 0;		
	end
	print @StartDate   			
end

The above query print the date which you show in your question.

splitting days into intervals

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0