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.

Sowell

Answer 1

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.

Immunology

This will not work if the user's language is not English, which is easily solved by changing 5-Jan-1900 to 19000105. But relying on 'magic' numbers and dates in code is a rather dubious practice because it's not at all obvious to anyone reading the code what they actually mean. Especially if the original programmer doesn't leave any comments, it can be extremely difficult to maintain code like this. – Abacist

Not intentionally :-) FYI, YYYY-MM-DD is not a good format either, this fails: set language french; select cast('2012-12-31' as datetime);. You should be using YYYYMMDD as I mentioned in my other comment to you. Although to be honest I have no idea why anyone would prefer this solution to a calendar table (nothing personal). – Abacist

I think that anyone who is reading this and who's target language or culture is not as outlined here will understand that they should amend it as necessary. For the vast majority of people who are reading this (in english) the answer is appropriate. So while technically you are absolutely correct (hat off) I don't think it's absolutely necessary to labour Ad Nauseum the point about date format differences quite so literally (so to speak). I agree with you on a Calendar table or Date Dimension, however not everyone has the luxury of being able to alter a schema as and when we'd like. – Freezing

This solution works great for me! I just changed the date format because I use french language. But I don't quite understand why it works! I can't quite understand even if I tried to separate every parts... I'm going to find out because I want to leave a good comment in my code. – Pyrexia

Answer 2

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.

Abacist

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

Araujo

do we need eomonth function that returns End date of a month ? the arguments year, month is OK , what for 1 argument is passed ? – Chapen

Answer 4

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

Roadhouse