T-SQL Function for Getting Business Dates Between a Given Range

29. October 2009 Bart X. Tubalinal SQL Comments (0)

Here's a really simple T-SQL function for getting a list of business dates (Monday - Friday) that fall within a given date range. For example, say I wanted to find the business dates between the dates 10/21/2009 - 10/30/2009. This function will return the following results:

10/21/2009
10/22/2009
10/23/2009
[10/24/2009 is skipped]
[10/25/2009 is skipped]
10/26/2009
10/27/2009
10/28/2009
10/29/2009
10/30/2009

I just thought this function might be helpful for others since I couldn't find anything else out there that someone else had written to accomplish this (easy as it is). There are plenty of T-SQL scripts that return the number of business days between a date range, I just couldn't find one that actually returned the business dates.

Here's the script:

CREATE FUNCTION BusinessDatesInDateRange
(
    @BeginDate DATETIME, @EndDate DATETIME
)

RETURNS @BusinessDates TABLE (BusinessDate DATETIME)
AS    BEGIN 
        DECLARE @CurrentDate DATETIME         
        SET @CurrentDate = @BeginDate        

        WHILE (@CurrentDate <= @EndDate) 
        BEGIN 
            -- 1: Sunday 7: Saturday 
            IF DATEPART(WEEKDAY, @CurrentDate) <> 1 AND DATEPART(WEEKDAY, @CurrentDate) <> 7 
            BEGIN 
                INSERT INTO @BusinessDates(BusinessDate) VALUES (@CurrentDate); 
            END             

            SET @CurrentDate = DATEADD(D, 1, @CurrentDate)             
        END

    RETURN 
    END

GO

Sample call to execute:

select * from dbo.BusinessDatesInDateRange('10/21/2009', '10/30/2009');

Tags: