How do you count the number of days between two dates excluding the extra day in leap years

Question

In the picture I count the number of days between two dates excluding the extra day of the leap years.

How can this be done in SQL Server 2008 R2?

Answer 1

You could build yourself a calendar table storing one row for each date along with the extra information you need about that date. To support your query it could look like.

create table Calendar
(
  TheDate date primary key,
  LeapDay bit not null
)

Your query would then be.

select count(*)
from Calendar
where TheDate >= @StartDate and
      TheDate < @EndDate and
      LeapDay = 0

One way to fill your calendar table with some data:

with Numbers(Number) as
(
  select top(11000) row_number() over(order by 1/0)
  from sys.all_objects as o1, sys.all_objects as o2
), Dates(TheDate) as
(
  select dateadd(day, Number-1, cast('2000-01-01' as date))
  from Numbers
)
insert into Calendar(TheDate, LeapDay)
select TheDate,
       case when datepart(month, TheDate) = 2 and 
                 datepart(day, TheDate) = 29
         then 1
         else 0
      end
from Dates

If you don't want to create a permanent table to support your query you can build one in a CTE.

with Dates(TheDate) as
(
  select top(datediff(day, @StartDate, @EndDate)) 
    dateadd(day, row_number() over(order by 1/0)-1, @StartDate)
  from sys.all_objects as o1, sys.all_objects as o2
)
select count(*)
from Dates as D
where not (datepart(month, D.TheDate) = 2 and datepart(day, D.TheDate) = 29);

SQL Fiddle