How to get date of last week's X day for any month from provided date or from GetDate() for current month

Question

Reverse question is - How to determine if the date is in last week of month?

For example, using GetDate() how would I know that today's date or given date is in last week of the month. As week number in MSSQL can be 4 or 5.

Suppose I want to get date of last Saturday of every month. But in SQL week number 5 (which is last week of Dec 2013) for Dec 2013 don't have Friday. Last week of December ends with Tue on 31st. So, I need to skip Dec and go further for January 2014 but there also last week ends on Friday 31st. So, keep moving....

So, how to get date of last week's X day from given date or today's date?

Thanks.

Answer 1

You can use the code below. It is based on week starting on Monday and ending on Sunday:

declare @date as date = '20131230' -- this is the date you check

declare @lastdayofmonth as date = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@date)+1,0))
declare @lastweekstart as date = dateadd(d, -datepart(dw, @lastdayofmonth)+2, @lastdayofmonth)
if datepart(m, @lastdayofmonth) <> datepart(m, @lastweekstart)
    set @lastweekstart = dateadd(d, -7, @lastweekstart)

if @date >= @lastweekstart and @date <= @lastdayofmonth
    print 'Within last week'
else
    print 'Not within last week'