Date range function

 This function generates a table of date ranges excluding weekends.

USE [YourDB]
GO

/****** Object:  UserDefinedFunction [dbo].[GetDatesBetween]    Script Date: 18/01/2023 3:44:30 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


 
-- =============================================
-- Author:		KrishanG
-- Create date: 20/07/2022
-- Description:	Get the working days between two dates includes these dates as well
-- specify the date time as 
--	SET @StartDate = '2021/07/16'
--	SET @EndDate = '2021/07/27'
-- =============================================
CREATE FUNCTION dbo.GetWeekdaysBetween (@startDate DATE, @endDate DATE)
RETURNS @weekdays TABLE (date DATE)
AS
BEGIN
    DECLARE @currentDate DATE = @startDate;
    WHILE (@currentDate <= @endDate)
    BEGIN
        IF DATEPART(WEEKDAY, @currentDate) NOT IN (1,7)
        BEGIN
            INSERT INTO @weekdays (date) VALUES (@currentDate);
        END
        SET @currentDate = DATEADD(day, 1, @currentDate);
    END
    RETURN
END