Showing posts with label Nth week day. Show all posts
Showing posts with label Nth week day. Show all posts

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
CREATE 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
@TimeOffset datetime = '00:00'
)
RETURNS datetime
AS
BEGIN
DECLARE @StartDate datetime,@ResultDate datetime

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

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