In this example I'm going to give the query to find the business days between two days.
Use this function to calculate the number of business days excluding Saturday and Sunday. Also it will exclude start date and it will include end date.
go
-- Select [dbo].[CalculateBussinessDays] ('02/18/2021', '03/06/2021')
CREATE or ALTER FUNCTION [dbo].[CalculateBussinessDays] (
@StartDate DATETIME,
@EndDate DATETIME
)
returns INT AS
BEGIN
DECLARE @tempStartDate DATETIME= @StartDate;
DECLARE @tempEndDate DATETIME = @EndDate;
IF(@tempStartDate IS NULL
OR
@tempEndDate IS NULL)
BEGIN
RETURN NULL;
END
--To avoid negative values reverse the date if StartDate is grater than EndDate
IF(@StartDate > @EndDate)
BEGIN
SET @StartDate = @tempEndDate;
SET @EndDate = @tempStartDate;
END
DECLARE @Counter INT = Datediff(day,@StartDate ,@EndDate);
DECLARE @TempCounter INT = 0;
DECLARE @TotalBusinessDays INT = 0;
WHILE @Counter >= 0
BEGIN
IF(@TempCounter > 0 OR @Counter = 1) -- To ignore first day's calculation
Begin
SET @TotalBusinessDays = @TotalBusinessDays
+ (Iif(Datename(dw, Dateadd(day,@TempCounter,@StartDate))
IN('Monday', 'Tuesday','Wednesday','Thursday','Friday'),1,0))
END
SET @Counter = @Counter - 1
SET @TempCounter = @TempCounter +1
END
RETURN @TotalBusinessDays;
END
Top comments (2)
Nice! I needed a slightly more flexible version so I refactored to include parameters for defining which days are business days, and to exclude the first or last day. Instead of a loop I used a recursive CTE to get the dates so it could be joined for extra flexibility. You can easily modify this to be a procedure that returns all of the business days as a result set of dates. Just change the select list at the end to be
*
instead of@NumberOfBusinessDays = COUNT(*)
EDIT: Wrote this on SQL Server, not sure if there is anything T-Sql specific here that might not work on MySQL.
Awesome!!
Thanks lot for taking your time to do this.