gerald.drouin (8/12/2009)

---

declare @DayToFind tinyint, @AnyDayInAMonth datetime
select 	@DayToFind = 1, 		--Monday
	@AnyDayInAMonth = '2009-04-15'	--Target month

select dateadd(day, (datepart(d, @AnyDayInAMonth) - datepart(dw, @AnyDayInAMonth) - @@datefirst + @DayToFind) % 7, dateadd(day, 1-datepart(d, @AnyDayInAMonth), @AnyDayInAMonth))

Seems like a nice single-select statement to me.

gerald.drouin (8/13/2009)

---

Thanks for the catch on the negative numbers. I've added a 14 day offset to ensure the code always lands in the current month. Works for every day in 2009 now.

declare @DayToFind tinyint, @AnyDayInAMonth datetime
select  @DayToFind = 1,         --Monday
    @AnyDayInAMonth = '2009-04-15'  --Target month
select dateadd(day, (datepart(d, @AnyDayInAMonth) - datepart(dw, @AnyDayInAMonth) - @@datefirst + @DayToFind + 14) % 7, dateadd(day, 1-datepart(d, @AnyDayInAMonth), @AnyDayInAMonth))