chrisSpaceman chrisSpaceman - 2 months ago 10
SQL Question

A string of quarterly dates between a start and end date

I've got a recursive cte working to generate a list of dates between @startDate and @endDate, incrementing by quarters.

declare @startDate datetime
declare @endDate datetime

set @startDate= '01-jan-2014'
set @endDate= '01-jul-2017'

;With cte
As
( Select @startDate date1
Union All
Select DateAdd(Month,3,date1) From cte where date1 < @endDate
) select cast(cast( Year(date1)*10000 + MONTH(date1)*100 + 1 as
varchar(255)) as date) quarterlyDates From cte


This yields:

quarterlyDates
--------------
2014-01-01
2014-04-01
2014-07-01
2014-10-01 ...


I'd like to concatenate the output of the cte into a single string as follows:

"'01-jan-2014', '01-apr-2014, '01-jul-2014'..."


etc. I'm baffled by this last step - any help would be greatly appreciated!

Answer Source

Not sure why you want to... but just wrap that bottom cte and use stuff.

declare @table table(quarterlyDates date)
insert into @table
values
('2014-01-01'),
('2014-04-01'),
('2014-07-01'),
('2014-10-01')

SELECT stuff((
    SELECT ', ' + cast(quarterlyDates as varchar(max))
    FROM @table
    FOR XML PATH('')
    ), 1, 2, '')

And in your code... though the second CTE isn't necessary I leave it for clarity.

declare @startDate datetime
declare @endDate datetime

set @startDate= '01-jan-2014'
set @endDate= '01-jul-2017'

;With cte
As
( Select @startDate date1
Union All
Select DateAdd(Month,3,date1)   From cte where date1 < @endDate 
), 

cte2 as(
select cast(cast( Year(date1)*10000 + MONTH(date1)*100 + 1 as 
varchar(255)) as date) quarterlyDates From cte)

SELECT stuff((
    SELECT ', ' + cast(quarterlyDates as varchar(max))
    FROM cte2
    FOR XML PATH('')
    ), 1, 2, '');