gerald.drouin (8/12/2009)
declare @DayToFind tinyint, @AnyDayInAMonth datetimeselect @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,
There are some issues with your solution which you'll see if you set @AnyDayInAMonth to '2009-08-01'.
The problem is with the expression:
[font="Courier New"] datepart(d, @AnyDayInAMonth) - datepart(dw, @AnyDayInAMonth) - @@datefirst + @DayToFind[/font]
which can yield a negative value in some cases.
But still good to see an attempt using @@DATEFIRST to make the solution universal, without using loops, and without the horrible constant that I had in my solution.
Nigel