SQL business day calculator is a useful tool for businesses that need to calculate the number of working days between two dates, excluding weekends and holidays. This can be useful for calculating project timelines, estimating delivery dates, or determining employee leave entitlements.

One way to implement a business day calculator in SQL Server is to create a function that takes two input parameters, a start date and an end date, and returns the number of working days between those dates.

Example

Here's an example of a SQL Server function that calculates working days:

CREATE FUNCTION dbo.GetWorkingDays
(
@StartDate DATE,
@EndDate DATE
)
RETURNS INT
AS
BEGIN
DECLARE @WorkingDays INT = 0;
DECLARE @CurrentDate DATE = @StartDate;

WHILE @CurrentDate <= @EndDate
BEGIN
IF (DATEPART(WEEKDAY, @CurrentDate) NOT IN (1, 7)) -- Exclude weekends
BEGIN
IF (NOT EXISTS (SELECT * FROM dbo.Holidays WHERE HolidayDate = @CurrentDate)) -- Exclude holidays
BEGIN
SET @WorkingDays = @WorkingDays + 1;
END
END

SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate);
END

RETURN @WorkingDays;
END

In this function, we use a WHILE loop to iterate through each date between the start date and end date. We use the DATEPART function to check if the current date is a weekend (Saturday or Sunday) and exclude it from the calculation. We also check if the current date is a holiday by querying a separate table called dbo.Holidays. If the current date is not a weekend and not a holiday, we increment the @WorkingDays variable.

Here's an example of how to use the function:

SELECT dbo.GetWorkingDays('2023-03-28', '2023-04-03') AS WorkingDays;

This would return a value of 4, as there are four working days (March 28, 29, 30, and April 3) between the start and end dates. Note that April 2 is a Sunday and is therefore excluded from the calculation.