So 12 days between the two dates, or 11x 24 hour periods going on the way.
Im going to use the DateCreated and DateModified columns in this particular table to test the performance of this algorithm.
Select (datediff(dd, @StartDate, @EndDate) 1) -Number of days between Start and End - ( (datediff(wk, @StartDate, @EndDate) * 2) -Weekend Days (case when datename(dw, @StartDate) 'Sunday' then 1 else 0 END) -Taking into consideration the startdate being a Sunday (case when datename(dw, @EndDate) 'Saturday' then.
END/code It is called like this: codeselect The function trims off any weekend days from the start and end of the period since they aren't going to count towards our total anyway.It might seem like you mac os x 10.4 tiger 4xcd version iso repack should calculate the time of the date to make sure, but you dont you can simple 1 the days.Youll need an algorithm that performs well!Ive just executed it against that data-set using the DateCreated as the StartDate and DateModified as the end date and its executed in a split second!(There are no indexes against the date columns in case you wondered).Im not going to include the SQL to generate test data to test this against, as I simply dont have time.Create function datetime, @EndDate datetime).Heres the function for anyone interested (sorry about the name!).Returns INT, aS, begin - If the start date is a weekend, move it foward to the next weekday while datepart(weekday, @start_date) in (1,7) - Sunday, Saturday begin SET @start_date dateadd(d,1 start_date) END - If the end date is a weekend, move it back.Datediff : declare @StartDate datetime, @EndDate datetime, select @StartDate @EndDate select datediff(wk, @StartDate, @EndDate) * 2 -Weekend Days.If you have a holiday table, you can count the number of dates in that table who fall between the start and end dates and are a weekday "datepart(weekday, @holiday_date) not in (1,7 and then subtract the holidays from the total before returning.If you don't have any suitable table with a date sequence you can use a recursive common table expression to generate one on the fly like this: declare @start date set @start ' declare @end date set @end ' ;with dates (date) as ( select.GO - 1 day off, insert into EmployeeHolidayPlanning, select 1, ' :00 ' :59' - 5 days off.F_week_days_in_period start_date datetime, @end_date datetime).So that now shows we have one period of 7 days between the two days (which we multiply by 2) 2 days.
Begin, sET @Datei dateadd(D, @i, @DateBegin iF NOT exists(.