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');