sql - Get date for nth day of week in nth week of month -

i have column values '3rd-wednesday', '2nd-tuesday', 'every-thursday'.

i'd create column reads strings, , determines if date has come month, , if has, return date of next month. if has not passed yet month, return date month.

expected results (on 4/22/16) above be: '05-18-2016', '05-10-2016', '04-28-2016'.

i'd prefer mathematically , avoid creating calendar table if possible.

thanks.

partial answer, no means bug free.

this doesn't cater 'every-' entries, give inspiration. i'm sure there plenty of test cases fail on, , might better off writing stored proc.

i did try calculating day name , day number of first day of month, calculating next wanted day , applying offset, got messy. know said no date table cte simplifies things.

how works

a cte creates calendar current month of date , dayname. rather suspect parsing code pulls day name test data , joins cte. clause filters dates greater nth occurrence, , select adds 4 weeks if date has passed. or @ least that's theory :)

i'm using datefromparts simplify code, sql 2012 function - there alternatives on 2008.

select * #test (values ('3rd-wednesday'), ('2nd-tuesday'), ('4th-monday')) a(value)  set datefirst 1  ;with days (     select      cast(dateadd(month,datediff(month,0,getdate()),n.number) date) date,      datename(weekday, dateadd(month,datediff(month,0,getdate()),n.number)) dayname      master..spt_values n n.type = 'p' , n.number between 0 , 31 ) select      t.value,     case when min(d.date) < getdate() dateadd(week, 4, min(d.date)) else min(d.date) end date #test t join days d on reverse(substring(reverse(t.value), 1, charindex('-', reverse(t.value)) -1)) = d.dayname d.date >=     datefromparts(         year(getdate()),          month(getdate()),         1+ 7*(cast(substring(t.value, 1,1) int) -1)         ) group t.value    value         date ------------- ---------- 2nd-tuesday   2016-05-10 3rd-wednesday 2016-05-18 4th-monday    2016-04-25