Determine 3rd Friday of each month

Question

I need to determine the dates which are the "3rd Friday of each month" for a date range of "1.1.1996 - 30.8.2014" in SQL Server.

I expect I should use a combination of DENSE_RANK() and PARTITION BY() to set "rank = 3". However, I am new to SQL and unable to find the correct code.

Answer 1

Given:

• Friday is called "Friday"

• The 3rd Friday of the month will always fall from 15th-21st of the month

  select thedate
  from yourtable
  where datename(weekday, thedate) = 'Friday'
  and datepart(day, thedate)>=15 and datepart(day, thedate)<=21;
  

You could also use weekday with datepart(), but it's more readable with a name IMO. String comparisons will obviously be slower though.

Answer 2

In order to get a language/culture independent answer, you need to account for different weekday names and start of the week.

In Italy, Friday is "Venerdì" and the fisrt day of the week is Monday, not Sunday as in US.

1900-01-01 was a monday, so we can use this information to calculate the weekday in a locale-independent fashion:

WITH dates AS (
    SELECT DATEADD(day, number, GETDATE()) AS theDate
    FROM master.dbo.spt_values
    WHERE type = 'P'
)
SELECT theDate, DATENAME(dw, theDate), DATEPART(dw, theDate)
FROM dates
WHERE DATEDIFF(day, '19000101', theDate) % 7 = 4
    AND DATEPART(day, thedate)>=15 and DATEPART(day, thedate)<=21;

Answer 3

Another way, that uses Phil's answer as a base, and takes care of different setting:

select thedate
from yourtable
where (datepart(weekday, thedate) + @@DATEFIRST - 2) % 7 + 1 = 5   -- 5 -> Friday
  and (datepart(day, thedate) - 1) / 7 + 1 = 3 ;                   -- 3 -> 3rd week

The 5 code (if you want a weekday other than Friday) should be (the same as SET DATEFIRST codes):

1 for Monday
2 for Tuesday
3 for Wednesday
4 for Thursday
5 for Friday
6 for Saturday
7 for Sunday

---

You can also just use a "known good" date to be safe in the face of language settings. For example, if looking for Fridays, check a calendar and see that January 2nd 2015 was a Friday. The first comparison could then be written as:

DATEPART(weekday,thedate) = DATEPART(weekday,'20150102') --Any Friday

See also How to get the Nth weekday of a month by Peter Larsson.

Answer 4

I actually wrote an article about this type of calculation at here

Basically, you can use the following code to find the 3rd Friday of each month in any date range

USE TEMPDB
set nocount on;
IF OBJECT_ID('dbo.#t') is not null 
 DROP TABLE dbo.#t;
CREATE TABLE #t ([Date] datetime,
  [Year] smallint, [Quarter] tinyint, [Month] tinyint
, [Day] smallint -- from 1 to 366 = 1st to 366th day in a year
, [Week] tinyint -- from 1 to 54 = the 1st to 54th week in a year; 
, [Monthly_week] tinyint -- 1/2/3/4/5=1st/2nd/3rd/4th/5th week in a month
, [Week_day] tinyint -- 1=Mon, 2=Tue, 3=Wed, 4=Thu, 5=Fri, 6=Sat, 7=Sun
);
GO
USE TEMPDB
-- populate the table #t, and the day of week is defined as
-- 1=Mon, 2=Tue, 3=Wed, 4=Thu,5=Fri, 6=Sat, 7=Sun
;WITH   C0   AS (SELECT c FROM (VALUES(1),(1)) AS D(c)),
  C1   AS (SELECT 1 AS c FROM C0 AS A CROSS JOIN C0 AS B),
  C2   AS (SELECT 1 AS c FROM C1 AS A CROSS JOIN C1 AS B),
  C3   AS (SELECT 1 AS c FROM C2 AS A CROSS JOIN C2 AS B),
  C4   AS (SELECT 1 AS c FROM C3 AS A CROSS JOIN C3 AS B), 
  C5   AS (SELECT 1 AS c FROM C4 AS A CROSS JOIN C3 AS B),
  C6   AS (select rn=row_number() over (order by c)  from C5),
  C7   as (select [date]=dateadd(day, rn-1, '19000101') FROM C6 WHERE rn <= datediff(day, '19000101', '99991231')+1)

INSERT INTO #t ([year], [quarter], [month], [week], [day], [monthly_week], [week_day], [date])
SELECT datepart(yy, [DATE]), datepart(qq, [date]), datepart(mm, [date]), datepart(wk, [date])
     , datediff(day, dateadd(year, datediff(year, 0, [date]), 0), [date])+1
  , datepart(week, [date]) -datepart(week, dateadd(month, datediff(month, 0, [date]) , 0))+1
  , CASE WHEN datepart(dw, [date])+@@datefirst-1 > 7 THEN (datepart(dw, [date])+@@datefirst-1)%7
         ELSE datepart(dw, [date])+@@datefirst-1 END
 , [date]
FROM C7
    --where [date] between '19900101' and '20990101'; -- if you want to populate a range of dates
GO

select convert(char(10), [Date], 120) 
from #t
where Monthly_week=3
and week_day=5
and [date] between '2015-01-01' and '2015-12-31' -- change to your own date range

Answer 5

Yes, I know this is old post. Thought I'd provide a different slant on things despite its age. Heh... and my apologies. I just realized that I've nearly duplicate what @jyao posted above.

Based on the current edit of the OP's original question, I couldn't figure out why people where posting the answers they did.

Looking through the edits, I found the original question and have posted it below...

I have a time series ranging from 1.1.1996 - 30.8.2014 in an SQL database e.g. with table "db.dbo.datestable".

I need to determine the dates which are the "3rd Friday of each month" for this date range in SQL.

I expect I should use a combination of "DENSE_RANK()" and "PARTITION BY()" to set "rank = 3". However, I am new to SQL and unable to find the correct code.

Can you solve this problem?

The part of the original question that I've emphasized by bolding it seems to be the key. I could certainly be incorrect but it seems to me that the OP was stating that he has a "Calendar" table called "dbo.datestable" and, to me, that makes a huge amount of difference and I now understand why many of the answers are what they are including the one that generated the dates because it was posted on Nov 10th... one day after the final edit on the question, which removed the final vestiges of the reference to the "dbo.datestable".

Like I said, I could be wrong but here's my interpretation of the original question.

I have a "Calendar" table called "dbo.datestable". Given any range of dates covered by that table, how can I return just the dates that are the 3rd Friday of each month within that given date range?

Since the conventional methods for doing this have already been covered, I'll add an alternative that could be helpful for some.

Let's simulate a couple of the columns that I think the OP will already have in his table. Of course, I'm guessing at the column names. Please sub whatever the equivalent columns are for your "Calendar" table. Also, I'm doing this all in TempDB so I don't take a chance of interfering with someone's real "Calendar" table.

--=================================================================================================
--      Simulate just a couple of the necessary columns of the OPs "Calendar" table.
--      This is not a part of the solution.  We're just trying to simulate what the OP has.
--=================================================================================================
--===== Variables to control the dates that will appear in the "Calendar" table.
DECLARE  @StartDT   DATETIME
        ,@EndDT     DATETIME
;
 SELECT  @StartDT = '1900' --Will be inclusive start of this year in calculations.
        ,@EndDT   = '2100' --Will be exclusive start of this year in calculations.
;
--===== Create the "Calendar" table with just enough columns to simulate the OP's.
 CREATE TABLE #datestable
        (
         TheDate    DATETIME NOT NULL
        ,DW         TINYINT  NOT NULL  --SQL standard abbreviate of "Day of Week"
        )
;
--===== Populate the "Calendar" table (uses "Minimal Logging" in 2008+ this case).    
   WITH cteGenDates AS
(
 SELECT TOP (DATEDIFF(dd,@StartDT,@EndDT)) --You can use "DAY" instead of "dd" if you prefer. I don't like it, though.
        TheDate = DATEADD(dd, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1, @StartDT)
   FROM      sys.all_columns ac1
  CROSS JOIN sys.all_columns ac2
)
 INSERT INTO #datestable WITH (TABLOCK)
 SELECT  TheDate
        ,DW = DATEDIFF(dd,0,TheDate)%7+1 --Monday is 1, Friday is 5, Sunday is 7 etc.
   FROM cteGenDates
 OPTION (RECOMPILE) -- Help keep "Minimal Logging" in the presence of variables.
;
--===== Add the expected named PK for this example.
  ALTER TABLE #datestable 
    ADD CONSTRAINT PK_datestable PRIMARY KEY CLUSTERED (TheDate)
;

It's also a given that I don't know if the OP can make changes to his "Calendar" table so this might not help him but it may help others. With that in mind, let's add a DWoM (Day of Week for the Month) column. If you don't like the name, please feel free to change it to whatever you need on your own box.

--===== Add the new column.
  ALTER TABLE #datestable
    ADD DWOM TINYINT NOT NULL DEFAULT (0)
;

Next, we need to populate the new column. The OP had a sense of this in his original unadulterated post.

--===== Populate the new column using the CTE trick for updates so that
     -- we can use a Windowing Function in an UPDATE.
   WITH cteGenDWOM AS
(
 SELECT DW# = ROW_NUMBER() OVER (PARTITION BY DATEDIFF(mm,0,TheDate), DW
                                     ORDER BY TheDate)
        ,DWOM
   FROM #datestable
)
 UPDATE cteGenDWOM
    SET DWOM = DW#
;

Now, because that's a fixed length column, that just created a bunch of page splits so we need to rebuild the Clustered Index to "repack" the table to have as many rows per page as possible for the sake of performance.

--===== "Repack" the Clustered Index to get rid of the page splits we 
     -- caused by adding the new column.
  ALTER INDEX PK_datestable
     ON #datestable
        REBUILD WITH (FILLFACTOR = 100, SORT_IN_TEMPDB = ON)
;

Once that's done, queries that do things like returning the 3rd Friday of every month in a given date range become trivial and fairly obvious to read.

--===== Return the 3rd Friday of every month included in the given date range.
 SELECT *
   FROM #datestable
  WHERE TheDate >= '1996-01-01' --I never use "BETWEEN" for dates out of habit for end date offsets.
    AND TheDate <= '2014-08-30'
    AND DW      =  5 --Friday
    AND DWOM    =  3 --The 3rd one for every month
  ORDER BY TheDate
;

Answer 6

Here is a simple cut and paste solution. You could turn this into a function if you want.

Declare @CurrDate Date
Set @CurrDate = '11-20-2016'

declare @first datetime -- First of the month of interest (no time part)
declare @nth tinyint -- Which of them - 1st, 2nd, etc.
declare @dow tinyint -- Day of week we want
set @first = DATEFROMPARTS(YEAR(@CurrDate), MONTH(@CurrDate), 1) 
set @nth = 3
set @dow = 6
declare @result datetime
set @result = @first + 7*(@nth-1)
select  @result + (7 + @dow - datepart(weekday,@result))%7