Get the dates of the ‘x’ occurrence of the Weekday within the Month 0

You may want to find for example the date of the 4th Saturday in each month for a given year. This function came out of answering the question here:

I’ve created it as a Table Valued Function so you can bind it into any query you wish.


create function dbo.tvfn_dates_of_day_in_month_position (
	@year_start date,
	@wkday varchar(8),
	@occurrance tinyint
	returns table
	return (
		select dy_in_year, rn_dy_mth
		from (
			select dy_in_year, 
				   datename( weekday, dy_in_year ) as wkdy,
				   row_number( ) over ( partition by datepart( month, dy_in_year ), datename( weekday, dy_in_year ) order by dy_in_year ) as rn_dy_mth
			from (
				select dateadd( day, rn, @year_start ) as dy_in_year
				from (
					select row_number() over( order by object_id ) - 1 as rn
					from sys.columns
					) as rn
				where dateadd( day, rn, @year_start ) < dateadd( year, 1, @year_start )
				) as dy
			) as dy_mth
		where rn_dy_mth = @occurrance
		  and wkdy = @wkday

Calling the function is straightforward, example:

select *
from dbo.tvfn_dates_of_day_in_month_position( '2016-01-01', 'Saturday', 2 )
select *
from dbo.tvfn_dates_of_day_in_month_position( '2016-01-01', 'Saturday', 4 )
order by dy_in_year