Number of days in quarter, SQL Server

Question

I want to calculate the number of days per-quarter if start date and finish dates are given.

for example, one table has two columns, start date and finish date. start date = 1st september and finish is 14th november.

I want to calculate the number of days present in between these two days that are present in each quarter

Q3 - 30 days
Q4 - 45 days (for this scenario)

Regards.

Answer 1

declare @StartDate date='2012-09-01';
declare @EndDate date='2012-11-14';



select CEILING(month(dateadd(q,datediff(q,0,dateadd(dd,number ,@StartDate)),0))/3.0) as  QuarterNo,
        COUNT(*) as 'number of days'
 from   master..spt_values
 where  type='p'
 and    dateadd(dd,number ,@StartDate)<=@EndDate
 group by dateadd(q,datediff(q,0,dateadd(dd,number ,@StartDate)),0)

SQL fiddle demo