HeavenCore | IT Solutions Built on Open Source Software
View Thread
 Print Thread
Function to get the date of the Nth occurrence of a given day
admin
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
 
Jump to Forum