Calendar Table Function

,

I have seen time and again how members of the development team go through great lengths to work with dates and calendars. There are many ways to tackle these problems, but some are far more effecient than others.

This example uses an in-line table valued function to return a calendar table that can then be consumed by the development team to simplify some of the more complex business requirements we run into.

As written, this function utilizes system tables and a persisted HOLIDAY table that is maintained by the DBA team. This HOLIDAY table contains all non-business days.

Let's say we have a business report that needs to find the last day of the month to determine a range of dates. Once the calendar function has been created, a procedure can be written to find these dates like so:

Last Day in each month:
SELECT  DISTINCT MAX([Date]) OVER (PARTITION BY [Year],[MonthName]) AS [Date]
FROM    dbo.CALENDAR('01/01/2014','12/31/2014')
ORDER BY [Date]
Last Thursday in each month:
SELECT  DISTINCT MAX([Date]) OVER (PARTITION BY [Year],[MonthName],[WeekdayName]) AS [Date]
FROM    dbo.CALENDAR('01/01/2014','12/31/2014')
WHERE   [WeekdayName] = 'Thursday'
ORDER BY [Date]
First and Third Monday in each month:
SELECT  c.[Date]
FROM    dbo.CALENDAR('01/01/2014','01/01/2015') AS c
        JOIN
        (   SELECT  ROW_NUMBER() OVER (PARTITION BY [Year],[MonthName],[WeekdayName] ORDER BY [RID]) AS [ID]
                ,   [MonthName]
                ,   [WeekdayName]
                ,   RID
            FROM    dbo.CALENDAR('01/01/2014','01/01/2015')
        ) l on c.RID = l.RID
WHERE   l.[WeekdayName] = 'Monday' And
        l.ID in (1,3)
ORDER BY c.RID
Bi-weekly Fridays:
SELECT  c.[Date]
FROM    dbo.CALENDAR('01/01/2014','01/01/2015') AS c
WHERE   [WeekdayName] = 'Friday' And
        WeekofYear % 2 = 0
ORDER BY RID

I hope this helps simplify some of these tougher tasks you have in your environments. I know it has in mine.

CREATE FUNCTION dbo.CALENDAR 
(
    @StartDate DATETIME
,   @EndDate DATETIME
) 
RETURNS TABLE
AS
--====================================
-- Name: CALENDAR
-- Created: Chris Kutsch 12/29/2014
-- Usage: Returns a dynamic calendar for date manipulation
--====================================
RETURN  
(
    SELECT  tt.RID
        ,   DATEADD(DAY,tt.RID-1,@StartDate) AS [Date]
        ,   DATEPART(quarter,DATEADD(DAY,tt.RID-1,@StartDate)) AS [Quarter]
        ,   DATEPART(dayofyear,DATEADD(DAY,tt.RID-1,@StartDate)) AS [DayofYear]
        ,   DATEPART(WEEK,DATEADD(DAY,tt.RID-1,@StartDate)) AS [WeekofYear]
        ,   DATEPART(YEAR,DATEADD(DAY,tt.RID-1,@StartDate)) AS [Year]    
        ,   DATEPART(MONTH,DATEADD(DAY,tt.RID-1,@StartDate)) AS [Month]
        ,   DATEPART(DAY,DATEADD(DAY,tt.RID-1,@StartDate)) AS [Day]    
        ,   DATEPART(weekday,DATEADD(DAY,tt.RID-1,@StartDate)) AS [Weekday]
        ,   DATENAME(MONTH,DATEADD(DAY,tt.RID-1,@StartDate)) AS [MonthName]
        ,   DATENAME(weekday,DATEADD(DAY,tt.RID-1,@StartDate)) AS [WeekdayName]
        ,   CASE WHEN rh.[CALENDER_DATE] IS NULL THEN 1 ELSE 0 END AS [IsBusinessDay]
        ,   (RIGHT( 
                REPLICATE('0',(4)) +
                CONVERT([VARCHAR],DATEPART(YEAR,DATEADD(DAY,tt.RID-1,@StartDate)),0)
                ,(4)
             )+
             RIGHT(
                REPLICATE('0',(2)) +
                CONVERT([VARCHAR],DATEPART(MONTH,DATEADD(DAY,tt.RID-1,@StartDate)),0)
                ,(2)
             )
            ) AS [Vintage]
        
    FROM    ( SELECT ROW_NUMBER() OVER (ORDER BY [object_id]) AS [RID]
              FROM sys.all_objects WITH (NOLOCK)
            ) tt LEFT OUTER JOIN
            dbo.HOLIDAYS rh WITH (NOLOCK) ON DATEADD(DAY,tt.RID-1,@StartDate) = rh.[CALENDER_DATE]
            
    WHERE   DATEADD(DAY,tt.RID-1,@StartDate) <= @EndDate
    
)
 
GO

Rate

3.71 (14)

Share

Share

Rate

3.71 (14)