last friday of a given month in sql server

Question

How do i get the date for last friday of the month in T-SQL?

I will be passing the year and month as parameter,e.g, 201211. If I pass '201211' as parameter it should return me '20121130' as answer as it's the date of last friday of month of november'12.

Answer 1

This would be much simpler using a calendar table; after creating the appropriate columns for your own needs you can just write this:

select 
    max([Date])
from 
    dbo.Calendar
where 
    YearAndMonth = 201211 and 
    DayOfWeek = 'Friday'

A calendar table is generally a much better solution for determining dates than using functions because the code is much more readable and you can use your own definition of things like WeekNumber, FinancialQuarter etc. that vary widely between countries and even companies.

Answer 2

Declare @d1 datetime = '2019-12-23'
Declare @searchDay int = 2 -- monday
select DATEADD(DAY, @searchDay-DATEPART(WEEKday, DateADD(day,-1, DATEADD(month, DATEDIFF(MONTH, 0, @d1)+1, 0))),DateADD(day,-1, DATEADD(month, DATEDIFF(MONTH, 0, @d1)+1, 0)))

This will give you Date on last Monday of the month, you can change your search by changing value in @searchDay

Answer 3

I created a scalar function for this:

create function [dbo].[lastDWMonth]
    (
     @y int
    ,@m int
    ,@dw int
    )

returns date

as

begin

declare @d date

;with x as
    (
    select datefromparts(@y,@m,1) d

    union all

    select dateadd(day,1,d) from x where d < eomonth(datefromparts(@y,@m,1))
    )

select
    @d = max(d)
from
    x
where
    datepart(dw,d) = @dw

return @d

end