Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

Sweet and simple "first and last" weekday calculation

DECLARE @Year SMALLINT = 2011,
        @NumberOfYears TINYINT = 3
 
;WITH cteCalendar(FirstOfMonth, LastOfMonth)
AS (
        SELECT  DATEADD(MONTH, 12 * @Year + Number - 22801, 6) AS FirstOfMonth,
                DATEADD(MONTH, 12 * @Year + Number - 22800, -1) AS LastOfMonth
        FROM    master..spt_values
        WHERE   TYPE = 'P'
                AND number BETWEEN 1 AND 12 * @NumberOfYears
)
SELECT  DATEADD(DAY, DATEDIFF(DAY, 6, FirstOfMonth) / 7 * 7, 6) AS FirstSunday,
        DATEADD(DAY, DATEDIFF(DAY, 5, LastOfMonth) / 7 * 7, 5) AS LastSaturday
FROM    cteCalendar

Print | posted on Thursday, October 28, 2010 9:25 AM | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 ]

Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET