Nth occurrence of a weekday in a month and year

  • Comments posted to this topic are about the item Nth occurrence of a weekday in a month and year

  • IF OBject_ID('getNthOccuranceOfWeekDay') IS NOT NULL

    BEGIN

    DROP FUNCTION dbo.getNthOccuranceOfWeekDay

    END

    GO

    CREATE FUNCTION dbo.getNthOccuranceOfWeekDay

    (

    @year VARCHAR(4),

    @month TINYINT,

    @weekday TINYINT,

    @iOccAt TINYINT

    )

    RETURNS DATETIME

    AS

    BEGIN

    DECLARE @dt DATETIME, @dtNextMonth DATETIME,@retDate DATETIME,@WkTINYINT

    SELECT

    @dt= DATEADD(mm,@month-1,@year),--CAST(@year AS CHAR(4))+'-'+CAST(@month AS CHAR(2))+'-01'

    @dtNextMonth=DATEADD(mm,@month,@year),

    @Wk=CASE WHEN DATEPART(DW,@dt ) IN (7,1) THEN DATEDIFF(WK,@dt,@dtNextMonth) ELSE DATEDIFF(WK,@dt,@dtNextMonth) -1 END

    SELECT @iOccAt=CASE WHEN @iOccAt > @wk THEN @wk-1 ELSE @iOccAt-1 END

    SELECT

    @dt=

    CASE WHEN DATEPART(DW,@dt)> @WeekdayTHEN DATEADD(dd, 7*@iOccAt, DATEADD(dd, @weekday - DATEPART(DW,@dt)+ 7 , @dt))

    WHEN DATEPART(DW,@dt)< @WeekdayTHEN DATEADD(dd, 7*@iOccAt,DATEADD(dd, @weekday - DATEPART(DW,@dt) , @dt) )

    ELSE @dt

    END

    RETURN @dt

    END

    GO

    Regards,
    Mitesh OSwal
    +918698619998

  • Working great for 1,2,3 & 4th occurances, but if i pass in 5 for april 2010, i am getting the wrong date

    For example:

    SELECT dbo.getNthOccuranceOfWeekDay(YEAR(GETDATE()), MONTH(GETDATE()), 1, 5)

    Resulting in:

    2010-04-18 00:00:00.000

    Should be:

    2010-04-25 00:00:00.000 (because the 25th is the last occurance of sunday in April.

    Please advise.

    Edit: my above issue occurs with Mitesh Oswal's version only, the version from Rajneesh Kumardev does not suffer from this problem.

    Knock Knock, Who's There?, sp underscore, sp underscore who?
    spid1, spid2, spid3....

  • DECLARE @year VARCHAR(4),

    @month TINYINT,

    @weekday TINYINT,

    @iOccAt TINYINT

    DECLARE @dt DATETIME

    SELECT @iOccAt = 5,@weekday =6,@month = 5,@year = 2014

    select@dt= DATEADD(MM,@month-1,DATEADD(YYYY,@year-1900,0))+

    CASE WHEN DATEPART(DW,DATEADD(MM,@month-1,DATEADD(YYYY,@year-1900,0))) > @weekday

    THEN 7-DATEPART(DW,DATEADD(MM,@month-1,DATEADD(YYYY,@year-1900,0)))+@weekday

    ELSE @weekday - (DATEPART(DW,DATEADD(MM,@month-1,DATEADD(YYYY,@year-1900,0)))) END + (7*(@iOccAt-1))

    select @dt, DATENAME(Dw,@dt)

    Regards,
    Mitesh OSwal
    +918698619998

  • Thanks for the script. I like date sql.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply