Wednesday, September 14, 2005

TSQL date teaser. Answer for Q4

4) A slight variation from (3), given a date, find the second last Thursday
of the month.
E.G: Given 10-July-2005, second last Thursday of the month is 21-July-2005.

Your solution must also be able to find (N)th last (day of the week) of the month.
Where : N can be last week, second last week, third last 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 @DaysToMinus int
declare @NthWeek int
declare @TargetDayOfWeek int

select @myDate = '10-jul-2005'
select @NthWeek = 2
select @TargetDayOfWeek = 5

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

select @DaysToMinus = ((datepart(dw,@endOfMonth) + (7-@TargetDayOfWeek)) % 7)
select dateadd(d, -(@DaysToMinus + ((@nthWeek-1)*7)), @endOfMonth)



Post a Comment

<< Home