Every once in a while, we need to create a list of dates between 2 given dates. Mike McIntyre just posted an example of how to that by creating a temporary table (http://cs.vbcity.com/blogs/mike-mcintyre/archive/2008/12/31/welcome-2009-create-a-calendar-with-microsoft-transact-sql-how-to-iterate-days-in-a-series.aspx).

Starting with his sample, I transformed it into a UDF and then added a parameter that skips the weekend’s days.

Here is my UDF:

CREATE FUNCTION [dbo].[GetAllDates] 
(
 @StartDate DATETIME, 
 @EndDate DATETIME,
 @SkipWeekend BIT
)
RETURNS 
@Dates TABLE 
(
 CalendarDate DateTime
)
AS
BEGIN
 WHILE @StartDate <= @EndDate
 BEGIN
  IF (@SkipWeekend = 0)
   OR ((@SkipWeekend = 1) AND (DATEPART(weekday, @StartDate) NOT IN (1,7)))  
  BEGIN 
   INSERT INTO @Dates(CalendarDate)
   SELECT @StartDate
  END
  
  SET @StartDate = DATEADD(dd, 1, @StartDate)
 END
 RETURN 
END

Now to use it, you can use :

				
select * from dbo.GetAllDates('2009-01-01', '2009-03-31', 0)
select * from dbo.GetAllDates('2009-01-01', '2009-03-31', 1)