user3335081 user3335081 - 1 year ago 45
SQL Question

Get Quarter and Year between two dates

I'd like to retrieve the list of years and quarters between two dates.

For example, from 25/12/2015 to 06/30/2017, the result should look like:

Year Quarter

2015 4
2016 1
2016 2
2016 3
2016 4
2017 1
2017 2
2017 3

vkp vkp
Answer Source

You can use a tally table to do this.

declare @start date='2015-12-25';
declare @end date = '2017-06-30';

select distinct year(dateadd(day,rnum,@start)) yr,
datepart(quarter,dateadd(day,rnum,@start)) qtr
from (select row_number() over(order by (select null)) as rnum 
      from master..spt_values) t
where dateadd(day,rnum,@start) <= @end;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download