Function to get the date of the Nth occurrence of a given day
Ideal for writing a schedule / task system where tasks need to be setup for the last Sunday of the month for example.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[udf_getNthOccuranceOfWeekDay]
(
@year INT ,
@month TINYINT ,
@weekday TINYINT ,
@iOccAt TINYINT
)
RETURNS DATETIME
AS
BEGIN
DECLARE @dt DATETIME ,
@retDate DATETIME
SET @dt = CAST(@year AS CHAR(4)) + '-' + CAST(@month AS CHAR(2)) + '-01'
IF ( MONTH(DATEADD(DAY, @weekday - DATEPART(DW, @dt), @dt)) = @month )
BEGIN
IF ( MONTH(DATEADD(WEEK, @iOccAt - 1, DATEADD(DAY, @weekday - DATEPART(DW, @dt), @dt))) = @month )
SET @retDate = DATEADD(WEEK, @iOccAt - 1, DATEADD(DAY, @weekday - DATEPART(DW, @dt), @dt))
ELSE
SET @retDate = DATEADD(WEEK, @iOccAt - 2, DATEADD(DAY, @weekday - DATEPART(DW, @dt), @dt))
END
ELSE
BEGIN
IF ( MONTH(DATEADD(WEEK, @iOccAt, DATEADD(DAY, @weekday - DATEPART(DW, @dt), @dt))) = @month )
SET @retDate = DATEADD(WEEK, @iOccAt, DATEADD(DAY, @weekday - DATEPART(DW, @dt), @dt))
ELSE
SET @retDate = DATEADD(WEEK, @iOccAt - 1, DATEADD(DAY, @weekday - DATEPART(DW, @dt), @dt))
END
RETURN @retDate
END
GO
Jordon Pilling | Heavencore Administrator
|
