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

Question

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?

Why do you Apr 9th and 10th and so on? – Gordon Linoff Mar 22 '19 at 18:01 — Gordon Linoff, Mar 22 '19 at 18:01

score 2 · Accepted Answer · edited Mar 22 '19 at 18:16

2

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.

edited Mar 22 '19 at 18:16

Ilyes

14,640
4
29
55

answered Mar 22 '19 at 18:11

Gordon Linoff

1,242,037
58
646
786

Thanks, works well. Another question, what if I want to expand the end date to 2019-10-01? This breaks after the first month. – dragon_heart Mar 22 '19 at 19:53
@dragon_heart . . . You should ask another question. The one you asked here has been answered. – Gordon Linoff Mar 22 '19 at 20:02

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

1 Answers1