sql - Get quarter start/end dates for more than a year (start year to current year)

Question

I've been trying to get start and end dates range for each quarter given a specific date/year, like this:

 SELECT DATEADD(mm, (quarter - 1) * 3, year_date) StartDate,
       DATEADD(dd, 0, DATEADD(mm, quarter * 3, year_date)) EndDate
       --quarter QuarterNo
  FROM
(
  SELECT '2012-01-01' year_date
) s CROSS JOIN 
(
  SELECT 1 quarter UNION ALL
  SELECT 2 UNION ALL
  SELECT 3 UNION ALL
  SELECT 4
) q

which produces the following output:

2012-01-01 00:00:00 2012-04-01 00:00:00
2012-04-01 00:00:00 2012-07-01 00:00:00
2012-07-01 00:00:00 2012-10-01 00:00:00
2012-10-01 00:00:00 2013-01-01 00:00:00

Problem: I need to do this for a given start_date and end_date, the problem being the end_date=current_day, so how can I achieve this:

2012-01-01 00:00:00 2012-04-01 00:00:00
2012-04-01 00:00:00 2012-07-01 00:00:00
2012-07-01 00:00:00 2012-10-01 00:00:00
2012-10-01 00:00:00 2013-01-01 00:00:00
    ...   ...
2021-01-01 00:00:00   2021-01-06 00:00:00

question from:https://stackoverflow.com/questions/65598934/get-quarter-start-end-dates-for-more-than-a-year-start-year-to-current-year

Answer 1

I think here is what you want to do :

SET startdatevar AS DATEtime = '2020-01-10' 
;WITH RECURSIVE cte AS (

SELECT startdatevar AS startdate , DATEADD(QUARTER, 1  , startdatevar) enddate , 1 quarter
UNION ALL 
SELECT enddate , CASE WHEN DATEADD(QUARTER, 1  , enddate) > CURRENT_DATE() THEN GETDATE() ELSE DATEADD(QUARTER, 1  , enddate) END enddate, quarter + 1
FROM cte 
WHERE 
    cte.enddate <= CURRENT_DATE()
     and quarter < 4
)


SELECT * FROM cte

to use your code , if you want to have more than 4 quarters :

SET quarter_limit = DATEDIFF(quarter , <startdate>,<enddate>)
;WITH RECURSIVE cte(q, qDate,enddate) as
    (
        select 1,    
          DATEFROMPARTS(year('2012-01-01'::date), 1, 1) -- First quarter date
         ,time_slice('2012-01-01'::date, 3, 'MONTH', 'END')
         UNION ALL
         select q+1,
         DATEADD(q, 1, qdate) -- next quarter start date
         ,time_slice(qdate::date, (q+1)*3, 'MONTH', 'END')
         from cte
         where q < quarter_limit -- limiting the number of next quarters
         AND cte.endDate <= <enddate>
     )
     SELECT * FROM cte