How do I return the third Friday of the current month?

Question

Post reply

How do I return the third Friday of the current month?

adam-639168

Default port

Points: 1444
More actions
August 6, 2009 at 10:12 am

#156072

Hello. I need to be able to return the third Friday of the current month.
e.g. if I run the query today, it will return 8/21/09
Any help is appreciated. Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i had the first monday of this month as an already saved snippet;

her's the step by step on how i got the third friday:

now instead of adding two weeks as a separate step, you could add 18(14 + 4 days) in the second step where you add days, but i thought it was valuable to see it seperate.

--find the first business day (Monday) of this month

select DATEADD(wk,DATEDIFF(wk,0,dateadd(dd,6 - datepart(day,getdate()),getdate())), 0)

--results: 2009-08-03 00:00:00.000 a monday

--add 4 days of it to get the first friday

select DATEADD(dd,4,DATEADD(wk,DATEDIFF(wk,0,dateadd(dd,6 - datepart(day,getdate()),getdate())), 0))

--results: 2009-08-07 00:00:00.000, the first friday

--add 2 weeks to it to get the third friday

select DATEADD(wk,2,DATEADD(dd,4,DATEADD(wk,DATEDIFF(wk,0,dateadd(dd,6 - datepart(day,getdate()),getdate())), 0)) )

--results: 2009-08-21 00:00:00.000 the third friday

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

GSquared SSC Guru Points: 260824 More actions · Answer 2

If you have a Numbers table, you can use this:

;with

DatesInMonth (Date) as

(select

dateadd(day, Number-1, dateadd(month, datediff(month, 0, getdate()), 0)) as Date

from dbo.Numbers

where Number <= 32),

Weekdays (Date, Seq, InvSeq, WDay) as

(select

Date,

row_number() over (partition by datepart(weekday, Date) order by Date),

row_number() over (partition by datepart(weekday, Date) order by Date desc),

datepart(weekday, Date)

from DatesInMonth)

select Date

from Weekdays

where Seq = 3

and WDay = 6;

You can use InvSeq to find the last Friday in a month (InvSeq = 1 for the last). You can use parameters/variables for the ordinal (3rd) and the weekday in the final Where clause.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

adam-639168 Default port Points: 1444 More actions · Answer 3

Thanks for both of your responses.

Lowell, thanks for the explanation. Made sense when you put it so simply.