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
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
No comments:
Post a Comment