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.

You could split the period specified by the two dates into subranges based on the quarters spanned, similarly to how [this answer](http://stackoverflow.com/questions/10796239/how-to-split-the-date-into-monthwise/10810278#10810278 "How to split the date into monthwise") does a monthly split. Once you've got the subranges, you can get `DATEDIFF(DAY, SubrangeStart, SubrangeEnd)` for every subrange included. — Andriy M, Nov 15 '12 at 05:49

Joe G Joseph · Answer 1 · 2012-11-14T11:27:51.620

4

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

edited Nov 14 '12 at 11:27

answered Nov 14 '12 at 11:22

Joe G Joseph

23,518
5
56
58

score 2 · Answer 2 · answered Nov 14 '12 at 11:15

You can use a recursive query to get this. This generates the list of dates between your start and end date and then gets the count of days per quarter:

;with cte (start, enddate) as
(
  select startdate, enddate
  from yourtable
  union all
  select dateadd(dd, 1, start), enddate
  from cte
  where dateadd(dd, 1, start) <= enddate
)
select datepart(q, start) Quarter, count(datepart(q, start)) NoDays
from cte
group by datepart(q, start)

See SQL Fiddle with Demo

Number of days in quarter, SQL Server

2 Answers2

SQL fiddle demo