RE: Nth occurence of a day in a month/year

SSC Guru

Points: 1003872

August 22, 2009 at 2:09 pm

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)