10

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.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
Abhishek Arora
  • 111
  • 1
  • 1
  • 4

4 Answers4

11

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.

Ciarán
  • 3,017
  • 1
  • 16
  • 20
  • 1
    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. – Pondlife Dec 21 '12 at 15:14
  • 1
    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). – Pondlife Dec 21 '12 at 15:32
  • 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. – Ciarán Dec 21 '12 at 16:09
  • 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. – Danielle Paquette-Harvey Jul 04 '13 at 19:37
4

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.

Community
  • 1
  • 1
Pondlife
  • 15,992
  • 6
  • 37
  • 51
2

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
Shaun
  • 21
  • 1
  • do we need eomonth function that returns End date of a month ? the arguments year, month is OK , what for 1 argument is passed ? – Sham Yemul Jan 29 '20 at 06:22
1
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

Abdul Qadir Memon
  • 950
  • 1
  • 12
  • 27