Generating a List of Calendar Dates

  • Article

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