>SELECT SUM(DATEDIFF(d,Holiday.Start,Holiday.End)) >>
>SELECT SUM(DATEDIFF(s,Holiday.Start,Holiday.End)) >>
declare @Holiday table ([Name] varchar(32), [Start] datetime, [End] datetime) insert into @Holiday ([Name], [Start], [End]) values ('Christmas', '2014-12-24', '2015-12-24') insert into @Holiday ([Name], [Start], [End]) values ('New Year', '2014-01-01', '2015-01-01') SELECT [Holiday].[Name], SUM(DATEDIFF(d, [Holiday].[Start], [Holiday].[End])) from @Holiday Holiday group by [Holiday].[Name] -- Christmas 365 -- New Year 365 SELECT [Holiday].[Name], SUM(DATEDIFF(s, [Holiday].[Start], [Holiday].[End])) from @Holiday Holiday group by [Holiday].[Name] -- Christmas 31536000 -- New Years 31536000