CODEWITHSUNDEEP
sql-servert-sqlsql-date-functions
Abhishek Arora
Abhishek Arora

Reputation: 111

last friday of a given month in sql server

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.

Upvotes: 10

Views: 16089

Answers (4)

Abdul Qadir Memon
Abdul Qadir Memon

Reputation: 1019

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

Upvotes: 1

Shaun
Shaun

Reputation: 21

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

Upvotes: 2

Pondlife
Pondlife

Reputation: 16240

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.

Upvotes: 4

Ciar&#225;n
Ciar&#225;n

Reputation: 3057

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.

Upvotes: 11

Related Questions