SDU Tools: Dates Between No Weekends

The DatesBetweenNoWeekends function in our free SDU Tools for developers and DBAs, is really popular. It provides a range of dates between starting and ending dates, ignoring weekends.

DatesBetweenNoWeekends is a simple table-valued function that takes two parameters:

@StartDate – the first date to return
@EndDate – the last date to return

The columns returned are:

DateNumber – a sequential number for the dates returned
DateValue – the date

Find out more

You can see it in action in the main image above, and in the video here. The full current version of the code is also shown below:

You can use our tools as a set or as a great example of how to write functions like these.

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

Latest version of the code

Note: the code might wrap when displayed below.

CREATE  FUNCTION [SDU_Tools].[DatesBetweenNoWeekends]
(
    @StartDate date,
    @EndDate date 
)
RETURNS @Dates TABLE
(
    DateNumber int IDENTITY(1,1) PRIMARY KEY,
    DateValue date
)
AS
-- Function:      Returns a table of dates excluding weekends
-- Parameters:    @StartDate date => first date to return
--                @EndDate => last date to return
-- Action:        Returns a table of dates between the two dates supplied (inclusive)
--                but excluding Saturday and Sunday
-- Return:        Rowset with DateNumber as int and DateValue as a date
-- Refer to this video: https://youtu.be/m5GtvUHXOFQ
--
-- Test examples: 
/*

SELECT * FROM SDU_Tools.DatesBetweenNoWeekends('20200101', '20200131') ORDER BY DateValue;
SELECT * FROM SDU_Tools.DatesBetweenNoWeekends('20200131', '20200101') ORDER BY DateValue;

*/
BEGIN
    DECLARE @CurrentValue date = @StartDate;

    WHILE @CurrentValue <= @EndDate 
    BEGIN
        IF DATEPART(weekday, @CurrentValue) NOT IN (DATEPART(weekday, '19000107'), DATEPART(weekday, '19000106'))
        BEGIN
            INSERT @Dates (DateValue) VALUES (@CurrentValue);
        END;
        SET @CurrentValue = DATEADD(day, 1, @CurrentValue);
    END;

    RETURN;
END;
GO

CREATE FUNCTION [SDU_Tools].[DatesBetweenNoWeekends]

(

@StartDate date,

@EndDate date

)

RETURNS @Dates TABLE

(

DateNumber int IDENTITY(1,1) PRIMARY KEY,

DateValue date

)

-- Function: Returns a table of dates excluding weekends

-- Parameters: @StartDate date => first date to return

-- @EndDate => last date to return

-- Action: Returns a table of dates between the two dates supplied (inclusive)

-- but excluding Saturday and Sunday

-- Return: Rowset with DateNumber as int and DateValue as a date

-- Refer to this video: https://youtu.be/m5GtvUHXOFQ

-- Test examples:

SELECT * FROM SDU_Tools.DatesBetweenNoWeekends('20200101', '20200131') ORDER BY DateValue;

SELECT * FROM SDU_Tools.DatesBetweenNoWeekends('20200131', '20200101') ORDER BY DateValue;

BEGIN

DECLARE @CurrentValue date = @StartDate;

WHILE @CurrentValue <= @EndDate

BEGIN

IF DATEPART(weekday, @CurrentValue) NOT IN (DATEPART(weekday, '19000107'), DATEPART(weekday, '19000106'))

BEGIN

INSERT @Dates (DateValue) VALUES (@CurrentValue);

END;

SET @CurrentValue = DATEADD(day, 1, @CurrentValue);

END;

RETURN;

END;

Find out more

Latest version of the code

Leave a Reply Cancel reply