One last thing. Questions often come up where a count of days is needed excluding special days. You might find it handy to add a column to flag whether or not a day is a holiday or a weekend or a workday. Having such a column makes such queries much simpler.

It's easy to flag weekend days when you create the table. After that, you can update it with 'H' for holidays at your leisure.

;with tally (N) as (select top(10000) ROW_NUMBER() over(order by (select null)) from sys.columns cross join sys.objects)

select N as DateID, DATEADD(d,N,convert(date,'12/31/2015')) as MyDate

,case when DATEPART(dw,DATEADD(d,N,convert(date,'12/31/2015'))) in (1,7) then 'W' else '' end as SpecialDay

into #MyDates

from tally

create unique clustered index PK_#MyDates on #MyDates(MyDate)

-- example of counting days excluding weekends

declare @start date = '5/1/2016'

,@end date = '5/30/2016'

select sum(1) as Workdays

from #MyDates

where Mydate between @start and @end

AND specialday <> 'W'

Good luck.