Jonathan Yong

6) A slight variation from (5), given a date, find the second Thursday
of the month.
E.G: Given 10-July-2005, second Thursday of the month is 14-July-2005.

Your solution must also be able to find (N)th (day of the week) of the month.
Where : N can be first week, second week and so on.
day of week can be any day from Sunday to Saturday.

declare @startOfNextMonth datetime
declare @endOfMonth datetime
declare @myDate varchar(20)
declare @DaysToAdd int
declare @NthWeek int
declare @TargetDayOfWeek int

select @myDate = '10-July-2005'

select @TargetDayOfWeek = 5
select @NthWeek = 2

select @startOfMonth = dateadd(d, (day(@myDate)-1)* -1, @myDate)

select @DaysToAdd = (7 - ((datepart(dw,@startOfMonth) +
                        (7 - @TargetDayOfWeek)) % 7)) % 7

select dateadd(d, @DaysToAdd + ((@nthWeek-1)*7), @startOfMonth)

Labels: sql