CODEWITHSUNDEEP
sqlsql-server
dragon_heart
dragon_heart

Reputation: 69

SQL list recurring dates between two dates with end date as next start date

My question is similar to this Get a list of dates between two dates using a function

using this code for 10 days recurring: 

Declare @startDate datetime
Declare @endDate datetime


set @startDate= '03/01/2019 12:00:00'
set @endDate = '04/30/2019 12:00:00'


 ;WITH mycte AS
(
  SELECT CAST(@startDate AS DATETIME) DateValue
  UNION ALL
  SELECT  DateValue + 10
  FROM    mycte   
  WHERE   DateValue + 10 < @endDate - 1
)

SELECT  DateValue
FROM    mycte
OPTION (MAXRECURSION 0)

I get the ff result:

2019-03-20 12:00:00.000
2019-03-30 12:00:00.000
2019-04-09 12:00:00.000
2019-04-19 12:00:00.000

but I want the result to be : 

2019-03-20 12:00:00.000
2019-03-30 12:00:00.000
2019-03-31 12:00:00.000
2019-04-09 12:00:00.000
2019-04-10 12:00:00.000
2019-04-20 12:00:00.000
2019-04-21 12:00:00.000
2019-04-30 12:00:00.000

Is this possible with SQL?

Upvotes: 3

Views: 115

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

I find the problem rather arcane, but this seems to do what you want:

set @startDate= '2019-03-10 12:00:00';
set @endDate = '2019-04-30 12:00:00';


WITH mycte AS (
       SELECT CAST(@startDate AS DATETIME) as DateValue
       UNION ALL
       SELECT CONVERT(DATETIME, EOMONTH(DateValue)) + CONVERT(DATETIME, CONVERT(TIME, DateValue))
       FROM  mycte  
       WHERE MONTH(DateValue + 10) <> MONTH(DateValue) AND
             CONVERT(DATE, DATEVALUE) <> CONVERT(DATE, EOMONTH(DateValue)) AND
             DateValue < @endDate
       UNION ALL
       SELECT DateValue + 10
       FROM mycte
       WHERE DateValue + 10 < @endDate
      )
SELECT DISTINCT DateValue
FROM mycte
ORDER BY DateValue
OPTION (MAXRECURSION 0);

Here is a db<>fiddle.

Upvotes: 2

Related Questions