November 17, 2015
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: http://stackoverflow.com/questions/33694768/how-to-get-list-of-2nd-and-4th-saturday-dates-in-sql-server.
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 as 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 ); go
Calling the function is straightforward, example:
select * from dbo.tvfn_dates_of_day_in_month_position( '2016-01-01', 'Saturday', 2 ) union select * from dbo.tvfn_dates_of_day_in_month_position( '2016-01-01', 'Saturday', 4 ) order by dy_in_year