Microsoft Bob

Generating a List of Calendar Dates

For today, here’s a simple trick.  Ever need to get a list of all the calendar dates for a period?  This is very simple using a user defined function with a table.   Below is a simple version.  I have a more complex version I am using for my application that filters based on another table containing holidays, etc. 

-- =============================================
-- Author:        Bob Leithiser
-- Create date: 7/5/2009
-- Description:    Return a list of dates in table format for a specified date range
-- =============================================
CREATE FUNCTION [Util].[udf_GetCalendarDates]
(
    @StartDate DATE, 
    @EndDate DATE
)
RETURNS 
@CalendarDates TABLE 
(
    -- Add the column definitions for the TABLE variable here
    CalendarDate DATE
)
AS
BEGIN
    -- Fill the table variable with the rows for your result set
    DECLARE @CalendarDate DATE = @StartDate
    WHILE @CalendarDate  <= @EndDate
    BEGIN
        BEGIN
            INSERT INTO @CalendarDates (CalendarDate) VALUES (@CalendarDate)
        END
        SET @CalendarDate = DATEADD(DD,1,@CalendarDate)
    END
    RETURN 
END

GO


select * from util.udf_GetCalendarDates('20090101','20090130')

and now we get:

CalendarDate
2009-01-01
2009-01-02
2009-01-03
2009-01-04
2009-01-05
2009-01-06
2009-01-07
2009-01-08
2009-01-09
2009-01-10
2009-01-11
2009-01-12
2009-01-13
2009-01-14
2009-01-15
2009-01-16
2009-01-17
2009-01-18
2009-01-19
2009-01-20
2009-01-21
2009-01-22
2009-01-23
2009-01-24
2009-01-25
2009-01-26
2009-01-27
2009-01-28
2009-01-29
2009-01-30

Here’s an example where I found this useful.  I needed to generate a list of holidays to avoid processing of days not containing any business activity data as part of an analytic/simulation application. 

insert into Load.Holiday (ExchangeName,HolidayDate)
select '*', CalendarDate
from util.udf_GetCalendarDates('2007-05-01','2009-07-04')
where not exists
(select distinct marketdate from dbo.EquityHistory where MarketDate = CalendarDate )
and DATEPART(WEEKDAY,CalendarDate) NOT IN (1,7)

select * from Load.holiday

(20 row(s) affected)

ExchangeName    HolidayDate
*    2007-05-28
*    2007-07-04
*    2007-09-03
*    2007-11-22
*    2007-12-25
*    2008-01-01
*    2008-01-21
*    2008-02-18
*    2008-03-21
*    2008-05-26
*    2008-07-04
*    2008-09-01
*    2008-11-27
*    2008-12-25
*    2009-01-01
*    2009-01-19
*    2009-02-16
*    2009-04-10
*    2009-05-25
*    2009-07-03