Discussion Forum: Function to get the date of the Nth occurrence of a given day - HeavenCore | IT Solutions Built on Open Source Software

View Thread

Function to get the date of the Nth occurrence of a given day
admin	#1 Posted on 26-06-2010 18:02
Posts: 627 Joined: 19/11/2006 00:08	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. Code Download source 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
	Web

Jump to Forum