last friday of a given month in sql server

The 5 January 1900 was a Friday. This uses that a base date and calculates the last Friday in any given month though you must give it a date during the month rather than just the month itself. Replace the 2012-12-01 in this with a date in your month

SELECT DATEADD(DY,DATEDIFF(DY,'1900-01-05',DATEADD(MM,DATEDIFF(MM,0,'2012-12-01'),30))/7*7,'1900-01-05')

You can also use this to get the last Saturday by replacing the 1900-01-05 WITH 1900-01-06 etc.

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.Calendarwhere     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.

I created a scalar function for this:

create function [dbo].[lastDWMonth]    (     @y int    ,@m int    ,@dw int    )returns dateasbegindeclare @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    xwhere    datepart(dw,d) = @dwreturn @dend