How to generate series of month end dates between two dates

(1) By anonymous on 2022-08-26 07:44:40 [link] [source]

Trying to generate a column containing all month end dates between two dates, however using below commented month end calculation throws it in an endless loop until out of memory:

WITH RECURSIVE MonthEndDates(DateVal) AS (
	VALUES('2017-05-31')
	UNION ALL
	SELECT date(DateVal, 'start of month', '+1 month')
	--SELECT date(DateVal,'start of month','+1 month','-1 day')
	FROM MonthEndDates
	WHERE DateVal < '2018-01-01'
)
SELECT DateVal FROM MonthEndDates;

Philip

(2.1) By Ryan Smith (cuz) on 2022-08-26 10:57:50 edited from 2.0 in reply to 1 [link] [source]

You can't "build" on the previous date since it will be a month-end date and advancing can land you in a wrong month. Further to that, your date calculation as is will every time end in the same month, hence the infinite loop.

One solution is to simply start from the base date, and keep a counter of the dates, until a final month is reached. The counter starts at 2 because the first month is already handled by the first select:

WITH RECURSIVE MonthEndDates(DateVal, BaseDate, MonthNo, FinalMonth) AS (
	SELECT '2017-05-31', '2017-05-01', 2, '2018-01-01'
	UNION ALL
	SELECT date(BaseDate, '+' || MonthNo || ' month', 'start of month', '-1 day'), BaseDate, MonthNo+1, FinalMonth
	  FROM MonthEndDates
	 WHERE DateVal < FinalMonth
)
SELECT DateVal FROM MonthEndDates;


  -- DateVal     
  -- ------------
  -- 2017-05-31  
  -- 2017-06-30  
  -- 2017-07-31  
  -- 2017-08-31  
  -- 2017-09-30  
  -- 2017-10-31  
  -- 2017-11-30  
  -- 2017-12-31  
  -- 2018-01-31

In this similar example, I used the counter as the limit for 12 months, but the principle is the same:


WITH RECURSIVE MonthEndDates(DateVal, BaseDate, MonthNo) AS (
	SELECT '2017-05-31', '2017-05-01', 2
	UNION ALL
	SELECT date(BaseDate, '+' || MonthNo || ' month', 'start of month', '-1 day'), BaseDate, MonthNo+1
	  FROM MonthEndDates
	 WHERE MonthNo < 12
)
SELECT DateVal FROM MonthEndDates;


  -- DateVal     
  -- ------------
  -- 2017-05-31  
  -- 2017-06-30  
  -- 2017-07-31  
  -- 2017-08-31  
  -- 2017-09-30  
  -- 2017-10-31  
  -- 2017-11-30  
  -- 2017-12-31  
  -- 2018-01-31  
  -- 2018-02-28  
  -- 2018-03-31

(3.1) By Ryan Smith (cuz) on 2022-08-26 11:01:48 edited from 3.0 in reply to 2.0 [source]

In case you insist on only using dates to traverse the series, this uses a normal date to increment, and calculates from it the month-end. (Again avoiding the initial date already catered for by starting the increment date a month later):

WITH RECURSIVE MonthEndDates(DateVal, incDate) AS (
	SELECT '2017-05-31', '2017-06-01'
	UNION ALL
	SELECT date(incDate, '+1 month', 'start of month', '-1 day'), date(incDate, '+1 month')
	  FROM MonthEndDates
	 WHERE DateVal < '2018-04-31'
)
SELECT DateVal FROM MonthEndDates;


  -- DateVal     
  -- ------------
  -- 2017-05-31  
  -- 2017-06-30  
  -- 2017-07-31  
  -- 2017-08-31  
  -- 2017-09-30  
  -- 2017-10-31  
  -- 2017-11-30  
  -- 2017-12-31  
  -- 2018-01-31  
  -- 2018-02-28  
  -- 2018-03-31  
  -- 2018-04-30  
  -- 2018-05-31

EDIT: Fixing a mistake - Of course April only has 30 days, so changing the WHERE clause to:

WHERE DateVal < '2018-04-30'

will see the loop end at the correct month in stead of a month later.

(4) By anonymous on 2022-08-26 12:46:25 in reply to 3.1 [link] [source]

Much appreciated, for both solution explanation!!

Philip