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)ASBEGIN 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
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)