Get Quarters StartDate and EndDate from Year

Question

I am using SQL Server 2008. I want to get Start Date and End Date of all quarters of year. For example if I pass 2013 in query then output should like

StartDate                 EndDate                   QuarterNo
-------------------------------------------------------------
2013-04-01 00:00:00.000   2013-06-30 00:00:00.000      1
2013-07-01 00:00:00.000   2013-09-30 00:00:00.000      2
2013-10-01 00:00:00.000   2013-12-31 00:00:00.000      3
2014-01-01 00:00:00.000   2014-03-31 00:00:00.000      4

Because of Financial Year start from 1st April, I want to get 1st quarter start from 1st April. How can I get this output? Thanks for help...

Answer 1

select 
    dateadd(M, 3*number, CONVERT(date, CONVERT(varchar(5),@year)+'-1-1')),
    dateadd(D,-1,dateadd(M, 3*number+3, CONVERT(date, CONVERT(varchar(5),@year)+'-1-1'))),
    Number QuarterNo
from master..spt_values 
where type='p' 
and number between 1 and 4  

You'll probably want to use dates, not datetimes, otherwise nothing during the day of the last day of the quarter is included in your quarter (eg: 2013-06-30 14:15)

To go the other way, use datepart

select ((DATEPART(q,@date)+2) % 4)+1