chrisSpaceman - 7 months ago 32
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!

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, '');
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download