CODEWITHSUNDEEP
sql
Farhad
Farhad

Reputation: 13

How to select date between two date after 2 month using sql

Start date-01/01/2018, end date:01/10/2018. difference: 2 month,

Output:

01/01/2018
01/03/2018
01/05/2018
01/07/2018

Upvotes: 0

Views: 49

Answers (3)

IShubh
IShubh

Reputation: 364

Try this simple query-:

declare @startDate date,@EndDate date;
set @startDate='2018-01-01';
set @EndDate='2018-10-01';
while DATEADD(month,2,@startDate)<@EndDate
begin
print @startDate
set @startDate=DATEADD(month,2,@startDate)
end

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

you can try below

CREATE TABLE T(
  ID INT,
  [Start Date Time] DATETIME,
  [End Date Time] DATETIME
);
INSERT INTO T VALUES (1,'2018-01-01 13:00:00.000','2018-10-01 10:00:00.000');


;WITH CTE AS (
    SELECT ID,[Start Date Time] startTime,[End Date Time] endTime
    FROM T
    UNION ALL
    SELECT ID,CAST(CAST(DATEADD(month,2,startTime)AS DATE) AS DATETIME),endTime 
    FROM CTE
    WHERE CAST(CAST(DATEADD(month,2,startTime) AS DATE) AS DATETIME) < endTime
)
SELECT id,
       startTime AS 'Start Date Time',endTime

FROM CTE

http://sqlfiddle.com/#!18/f48ed/1

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

Try with this query:

With dt As
 (
 Select @Startdate As [TheDate]
 Union All
 Select DateAdd(month, 2, TheDate) From dt Where [TheDate] < @enddate
 ) 
select [TheDate] from dt

Upvotes: 1

Related Questions