Tuesday, February 9, 2010

Find out particular occurance of weekday in a month

The following function returns the Nth occurance of week day in a month

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME ='GetNthWeekday' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE='FUNCTION')
DROP FUNCTION dbo.GetNthWeekday
GO
ALTER FUNCTION dbo.GetNthWeekday
(
@date datetime,--Any date of month under consideration
@N int,-- Nth occurance
@WeekDay int--Which week day to look for (Sunday-1,Monday-2,..,Saturday-7)
--returns NULL when calculated date is out of scope of month
)
RETURNS datetime
AS
BEGIN
DECLARE @StartDate datetime,@ResultDate datetime

SET @StartDate=DATEADD(mm,DATEDIFF(mm,0,@date),0)

SELECT @ResultDate=DATEADD(
dd,
CASE WHEN DATEPART(DW,DATEADD(wk,@N-1,@StartDate)) > @WeekDay
THEN (7 - (DATEPART(DW,DATEADD(wk,@N-1,@StartDate))- @WeekDay))
ELSE (@WeekDay - DATEPART(DW,DATEADD(wk,@N-1,@StartDate)))
END,
DATEADD(wk,@N-1,@StartDate)
)
SET @ResultDate= CASE WHEN MONTH(@ResultDate) <> MONTH(@date)
THEN NULL
ELSE @ResultDate
END
RETURN @ResultDate
END