Nth occurence of a day in a month/year

  • Comments posted to this topic are about the item Nth occurence of a day in a month/year

  • Can't get this to work.

  • Why not just do (using the function definition and parameters (and the @firstDate variable) as given):

    set @firstDate = dateadd(dd,1-datepart(dd,@inputdate) ,@inputDate) -- ensures that we start with the first of the month

    select

    dateadd(wk, @occurAt - 1, -- adds the additional weeks, if any

    dateadd(dd, @day - datepart(dw, @firstDate)-- moves to the given day of week

    + case when @day < datepart(dw, dateadd(dd,1-datepart(dd,@firstdate) ,@firstDate)) then 7 else 0 end, -- ensures we don't go back to the last month

    @firstDate

    )

    )

  • Can't get this to work.

    How r u setting the parameter? Let me explain it.

    For example if you need date for 3rd Sunday of March 2009 then use as follows

    SELECT dbo.getDateAtNthOccurence('01/Mar/2009',1,3)

    The output will be 2009-03-15 (15th march 2009)

    The first parameter is any date of month/year for which we want the occurrence date.

    The second parameter is as follows (if you need sunday then send 1 as parameter value):

    1:Sunday, 2:Monday, 3:Tuesday, 4:Wednesday, 5:Thrusday, 6:Friday, 7:Saturday

    The third parameter is as follows (if you need 3rd sunday then send 3 as parameter value):

    1:First, 2:Second, 3:Third, 4:Fourth, >4:Last

    hope this will help you to get the output.

    cheers 🙂

  • michelle.baumgarten (8/21/2009)


    Can't get this to work.

    The code works fine for me. You need to post the exact code that you can't get to work so we can help.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • sknox (8/21/2009)


    Why not just do (using the function definition and parameters (and the @firstDate variable) as given):

    set @firstDate = dateadd(dd,1-datepart(dd,@inputdate) ,@inputDate) -- ensures that we start with the first of the month

    select

    dateadd(wk, @occurAt - 1, -- adds the additional weeks, if any

    dateadd(dd, @day - datepart(dw, @firstDate)-- moves to the given day of week

    + case when @day < datepart(dw, dateadd(dd,1-datepart(dd,@firstdate) ,@firstDate)) then 7 else 0 end, -- ensures we don't go back to the last month

    @firstDate

    )

    )

    I agree... the only desireable thing missing from the above code is for the "LAST" desired occurance. For example... for 2012-02-01 looking for Sunday(1) as the last occurance of a Sunday in the month (5), your good code returns the first Sunday of March instead of the last Sunday of February.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Since you asked --- I copied the code, entered '01/MAR/2009',1,3 for the parameters, and got a message saying:

    Incorrect syntax near keyword FUNCTION.

    A RETURN statement with a RETURN value cannot be used in this context.

    CREATE FUNCTION dbo.getDateAtNthOccurence

    (

    @inputDate DATETIME,

    @day TINYINT, -- 1:Sunday, 2:Monday, 3:Tuesday, 4:Wednesday, 5:Thrusday, 6:Friday, 7:Saturday

    @occurAt TINYINT -- 1:First, 2:Second, 3:Third, 4:Fourth, >4:Last

    )

    RETURNS DATETIME

    AS

    BEGIN

    DECLARE @counter TINYINT,

    @firstDate DATETIME,

    @lastDate DATETIME,

    @retDate DATETIME

    SELECT @counter = 1, @firstDate = DATEADD(dd,-(DAY(@inputDate)-1),@inputDate), @lastDate = DATEADD(dd,-1,DATEADD(MONTH,1,@firstDate))

    WHILE @firstDate<=@lastDate

    BEGIN

    IF(DATEPart(dw , @firstDate )=@day) and @counter<=@occurAt

    BEGIN

    SELECT @retDate = @firstDate,@counter = @counter+1

    END

    SET @firstDate = DATEADD(DAY,1,@firstDate)

    END

    -- Return the result of the function

    RETURN @retDate

    END

    GO

  • michelle.baumgarten (8/24/2009)


    Since you asked --- I copied the code, entered '01/MAR/2009',1,3 for the parameters, and got a message saying:

    Incorrect syntax near keyword FUNCTION.

    A RETURN statement with a RETURN value cannot be used in this context.

    CREATE FUNCTION dbo.getDateAtNthOccurence

    (

    @inputDate DATETIME,

    @day TINYINT, -- 1:Sunday, 2:Monday, 3:Tuesday, 4:Wednesday, 5:Thrusday, 6:Friday, 7:Saturday

    @occurAt TINYINT -- 1:First, 2:Second, 3:Third, 4:Fourth, >4:Last

    )

    RETURNS DATETIME

    AS

    BEGIN

    DECLARE @counter TINYINT,

    @firstDate DATETIME,

    @lastDate DATETIME,

    @retDate DATETIME

    SELECT @counter = 1, @firstDate = DATEADD(dd,-(DAY(@inputDate)-1),@inputDate), @lastDate = DATEADD(dd,-1,DATEADD(MONTH,1,@firstDate))

    WHILE @firstDate<=@lastDate

    BEGIN

    IF(DATEPart(dw , @firstDate )=@day) and @counter<=@occurAt

    BEGIN

    SELECT @retDate = @firstDate,@counter = @counter+1

    END

    SET @firstDate = DATEADD(DAY,1,@firstDate)

    END

    -- Return the result of the function

    RETURN @retDate

    END

    GO

    I suspect that you're changing the code above and then trying to run it as if it were a script. It will not work that way. Run the code as it is above to create the function and then run something like the following to use the function...

    SELECT dbo.getDateAtNthOccurence(GETDATE(),1,5)

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

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