Jonathan Yong

3). Given a date, find the last Friday of the month.
E.G: Given 10-July-2005, last Friday for the month is 29-July-2005.

declare @startOfNextMonth datetime
declare @endOfMonth datetime
declare @myDate varchar(20)
declare @DaysToMinus int

select @myDate = '10-jul-2005'

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

select @startOfNextMonth = dateadd(m, 1, @startOfMonth)
print  @startOfNextMonth

select @endOfMonth = dateadd(d, -1 , @startOfNextMonth)
print  @endOfMonth

select @DaysToMinus = -((datepart(dw,@endOfMonth) + 1) % 7)

select dateadd(d, @DaysToMinus, @endOfMonth)

Labels: sql