# Get all THIRD mondays on a given date range

• ### Question

• I need help on this. I want to capture the dates of all "third" Mondays only in February month that is within a given date range. For exmaple beg_date: 05 Mar 2012 end_date 21 Feb 2019. Thanks

Kahlua

• Edited by Monday, February 25, 2019 10:31 PM
Monday, February 25, 2019 9:51 PM

• It should not return the date '2019-02-18' when using the @beg_date = '20190219' and @end_date = '20190220'.

Kahlua

```DECLARE  @StartDate  DATETIME = '2019-02-19'
DECLARE @EndDate DATETIME = '2019-02-20'

--****  create a Number table
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n <101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)

,DateRange as
(
)

SELECT distinct DateAdd(Month, DateDiff(Month, 0, dt), 0)+6+14
-(DATEPART (Weekday, DateAdd(Month, DateDiff(Month, 0, dt), 0))
+@@DateFirst+4)%7 --Third Monday IN the month

from DateRange
WHERE month(dt)=2 and
-(DATEPART (Weekday, DateAdd(Month, DateDiff(Month, 0, dt), 0))
+@@DateFirst+4)%7 between @startdate  and @EndDate ```

• Marked as answer by Wednesday, February 27, 2019 8:09 PM
Wednesday, February 27, 2019 7:31 PM

### All replies

• Hi

private void button1_Click(object sender, EventArgs e)
{

TimeSpan ts = dateTimePicker2.Value.Subtract(dateTimePicker1.Value);
int totdays;
totdays = 0;
totdays = Convert.ToInt32(ts.TotalDays);
textBox1.Text =totdays.ToString()+1;

DateTime startDate;
DateTime endDate;

int x=0,mc=0;
startDate =dateTimePicker1.Value;
endDate = dateTimePicker2.Value;
while (startDate < endDate)
{
if (startDate.DayOfWeek == DayOfWeek.Monday)
{
++x;
}

mc = mc + x;
}
textBox4.Text =Convert.ToString(x);
}

Best Regards.

Monday, February 25, 2019 10:33 PM
• I would consider using a Calendar table for this. There surely can be shortcuts for this problem. But then you get that problem. And that problem. This article from Ed Pollack is a good start: https://www.sqlshack.com/designing-a-calendar-table/.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Monday, February 25, 2019 11:01 PM
• Hi pmak,

Would you like this one ?

```declare @beg_date date ='20120305'
declare @end_date date ='20190221'
; with cte as (
union all
),cte2 as (
select *, dense_rank()over(partition by year(Monday) order by Monday) as rn from cte where month(Monday)=2
)
select cast(Monday as date) as Monday  from cte2 where rn =3
OPTION(MAXRECURSION 0)
/*
Monday
----------
2013-02-18
2014-02-17
2015-02-16
2016-02-15
2017-02-20
2018-02-19
2019-02-18
*/
```

Best Regards,

Rachel

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

• Marked as answer by Tuesday, February 26, 2019 5:45 PM
• Unmarked as answer by Tuesday, February 26, 2019 10:36 PM
Tuesday, February 26, 2019 2:45 AM
• Thank you Rachel for your reply. Your solution will only work if the beg_date and the end_date "date range" stretches over the entire February month. For example if the beg_date is in the middle of the Feb such as 15 Feb 2019 and the end_date is 19 Feb 2019 and we know the 18 Feb 2019 is the third monday this February month then your SQL script will not pick up that date. Thanks.

Kahlua

• Edited by Tuesday, February 26, 2019 10:37 PM
Tuesday, February 26, 2019 10:24 PM
• Hi pmak,

```declare @beg_date date = '20190215'
declare @end_date date = '20190219'

; with cte as (
union all
),cte2 as (
select *, dense_rank()over(partition by year(Monday) order by Monday) as rn from cte where month(Monday)=2
)
select cast(Monday as date) as Monday  from cte2 where rn =3
OPTION(MAXRECURSION 0)
/*
Monday
----------
2019-02-18
*/```

Best Regards,

Rachel

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

• Marked as answer by Wednesday, February 27, 2019 4:52 PM
• Unmarked as answer by Wednesday, February 27, 2019 7:21 PM
Wednesday, February 27, 2019 1:33 AM
• Hello pmak,

I have tried to answer the question.

I have test on two date ranges:

Range 1: '2012-03-05' to '2019-02-21'

Range 2: '2019-02-15' to '2019-02-19'

This code works fine for both the test cases. Please test for different ranges.

Here's my code.

```Declare @Temp Table
(
ID Int Identity(1,1),
beg_date date,
end_date date
)
Insert @Temp
Values
('20120305','20190221'),
('20190215','20190219')

;With CTE (ID,dt,Mnth,Yr)
As
(
Select ID, Convert(date,Convert(varchar,Year(beg_date)) + '0101'),
1, YEAR(beg_date)
From @Temp
Union All
From CTE T1
Join @Temp T2
On T1.ID = T2.ID
And Year(dt) <= Year(end_date)
)
Select T3.*, T1.dt ThirdMonday, DATENAME(mm,T1.dt) MonthName, Year(T1.dt) Yr
From CTE T1
Join (
Select T1.ID, Max(T1.dt) dt
From CTE T1
Join (
Select ID, Mnth, Yr, dt
From CTE
Where datepart(dw,dt) = 2
) T2
On T1.ID = T2.ID
And T1.Yr = T2.Yr
And T1.Mnth = T2.Mnth
And T1.dt < T2.dt
Where datepart(dw,T1.dt) = 2
Group BY T1.ID, T1.Mnth, T1.Yr, T2.dt
Having Count(T2.dt) = 3
) T2
On T1.ID = T2.ID
And T1.dt = T2.dt
Join @Temp T3
On T1.ID = T3.ID
And T1.dt >= T3.beg_date And T1.dt <= T3.end_date
Where Datepart(mm,T1.dt) = 2
Order BY 1, 4
option (maxrecursion 0)```

Sharat

• Edited by Wednesday, February 27, 2019 9:38 AM
• Proposed as answer by Wednesday, February 27, 2019 4:26 PM
Wednesday, February 27, 2019 9:31 AM
• ```DECLARE  @StartDate  DATETIME = '2012-03-05'
DECLARE @EndDate DATETIME = '2019-02-19'

--****  create a Number table
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n <101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)

,DateRange as
(
)

SELECT distinct DateAdd(Month, DateDiff(Month, 0, dt), 0)+6+14
-(DATEPART (Weekday, DateAdd(Month, DateDiff(Month, 0, dt), 0))
+@@DateFirst+4)%7 --Third Monday IN the month

from DateRange
WHERE month(dt)=2```

• Marked as answer by Wednesday, February 27, 2019 7:14 PM
• Unmarked as answer by Wednesday, February 27, 2019 7:15 PM
Wednesday, February 27, 2019 3:05 PM
• If you are interested, you can check this out with a UDF NthDayOfWeekOfMonth from

https://www.red-gate.com/simple-talk/sql/learn-sql-server/robyn-pages-sql-server-datetime-workbench/

In your case, you use this query with the function:

```DECLARE  @StartDate  DATETIME = '2012-03-05'
DECLARE @EndDate DATETIME = '2019-02-19'

--****  create a Number table
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n <101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)

,DateRange as
(
)

select distinct  dbo.NthDayOfWeekOfMonth (year(dt),'Feb','Mon',3)
from DateRange/*2012-02-20 00:00:00.0002013-02-18 00:00:00.0002014-02-17 00:00:00.0002015-02-16 00:00:00.0002016-02-15 00:00:00.0002017-02-20 00:00:00.0002018-02-19 00:00:00.0002019-02-18 00:00:00.000*/```

Wednesday, February 27, 2019 3:17 PM
• Thank you Sharat for your reply. I tried the following StartDate '2019-02-19' and EndDate '2019-02-20', unfortunately, your SQL script returns '2019-02-18' that it should not have as this data is outside the specific date range.

﻿

Kahlua

Wednesday, February 27, 2019 7:19 PM
• It should not return the date '2019-02-18' when using the @beg_date = '20190219' and @end_date = '20190220'.

Kahlua

Wednesday, February 27, 2019 7:23 PM
• It should not return the date '2019-02-18' when using the @beg_date = '20190219' and @end_date = '20190220'.

Kahlua

```DECLARE  @StartDate  DATETIME = '2019-02-19'
DECLARE @EndDate DATETIME = '2019-02-20'

--****  create a Number table
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n <101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)

,DateRange as
(
)

SELECT distinct DateAdd(Month, DateDiff(Month, 0, dt), 0)+6+14
-(DATEPART (Weekday, DateAdd(Month, DateDiff(Month, 0, dt), 0))
+@@DateFirst+4)%7 --Third Monday IN the month

from DateRange
WHERE month(dt)=2 and
-(DATEPART (Weekday, DateAdd(Month, DateDiff(Month, 0, dt), 0))
+@@DateFirst+4)%7 between @startdate  and @EndDate ```

• Marked as answer by Wednesday, February 27, 2019 8:09 PM
Wednesday, February 27, 2019 7:31 PM
• Thank you Jingyang for your SQL script. The script works, however it is super complicate, I tried to understand it and I am lost in the last "Select distinct" statement (Third Monday In the Month Logic) on your script. Please can you explain, I'd like to understand the logic before I use it in a suitation.

Kahlua

• Edited by Thursday, February 28, 2019 9:37 PM
Thursday, February 28, 2019 8:34 PM
• Robyn Page wrote the solution back in 2006. You can find the logic in her post(long reading):

https://www.red-gate.com/simple-talk/sql/learn-sql-server/robyn-pages-sql-server-datetime-workbench/

My query's first two parts are creating a list of date for your date range on the fly (a calendar table will make it really simple).

The core part:

```SELECT distinct DateAdd(Month, DateDiff(Month, 0, dt), 0)+6+14
-(DATEPART (Weekday, DateAdd(Month, DateDiff(Month, 0, dt), 0))
+@@DateFirst+4)%7 --Third Monday IN the month

from DateRange```

The where clause is to filter out the final result you need.

Hope this helps.

Thursday, February 28, 2019 9:56 PM
• Thanks and WOW to Robyn's work. Very impressive.

Kahlua

Thursday, February 28, 2019 10:31 PM
• Thank you Sharat for your reply. I tried the following StartDate '2019-02-19' and EndDate '2019-02-20', unfortunately, your SQL script returns '2019-02-18' that it should not have as this data is outside the specific date range.

﻿

Kahlua

Kahlua,

I am answering to an answered question only because you have said my script returns '2019-02-18' (out of range) when the StartDate = '2019-02-19-' and EndDate = '2019-02-20'. I think this is not the case. It does return anything. Please see here. I will change the test data only. added '20100219','20190220' ID = 3.

The code is the same, no changes.

```Set Nocount On
GO
Declare @Temp Table
(
ID Int Identity(1,1),
beg_date date,
end_date date
)
Insert @Temp
Values
('20120305','20190221'), --same record
('20190215','20190219'), --same record

;With CTE (ID,dt,Mnth,Yr)
As
(
Select ID, Convert(date,Convert(varchar,Year(beg_date)) + '0101'),
1, YEAR(beg_date)
From @Temp
Union All
From CTE T1
Join @Temp T2
On T1.ID = T2.ID
And Year(dt) <= Year(end_date)
)
Select T3.*, T1.dt ThirdMonday, DATENAME(mm,T1.dt) MonthName, Year(T1.dt) Yr
From CTE T1
Join (
Select T1.ID, Max(T1.dt) dt
From CTE T1
Join (
Select ID, Mnth, Yr, dt
From CTE
Where datepart(dw,dt) = 2
) T2
On T1.ID = T2.ID
And T1.Yr = T2.Yr
And T1.Mnth = T2.Mnth
And T1.dt < T2.dt
Where datepart(dw,T1.dt) = 2
Group BY T1.ID, T1.Mnth, T1.Yr, T2.dt
Having Count(T2.dt) = 3
) T2
On T1.ID = T2.ID
And T1.dt = T2.dt
Join @Temp T3
On T1.ID = T3.ID
And T1.dt >= T3.beg_date And T1.dt <= T3.end_date
Where Datepart(mm,T1.dt) = 2
Order BY 1, 4
option (maxrecursion 0)```

Results:

```ID          beg_date   end_date   ThirdMonday MonthName                      Yr
----------- ---------- ---------- ----------- ------------------------------ -----------
1           2012-03-05 2019-02-21 2013-02-18  February                       2013
1           2012-03-05 2019-02-21 2014-02-17  February                       2014
1           2012-03-05 2019-02-21 2015-02-16  February                       2015
1           2012-03-05 2019-02-21 2016-02-15  February                       2016
1           2012-03-05 2019-02-21 2017-02-20  February                       2017
1           2012-03-05 2019-02-21 2018-02-19  February                       2018
1           2012-03-05 2019-02-21 2019-02-18  February                       2019
2           2019-02-15 2019-02-19 2019-02-18  February                       2019```

As you can see there is no record for ID = 3 (StartDate = '20190219', EndDate = '20190220').

This solution handles multiple StartDates and EndDates in a table. Other solutions were based in variable @StartDate and @EndDate.

I did not understand why it returns '2019-02-18' on your system?

Sharat

• Edited by Saturday, March 2, 2019 2:23 PM
Saturday, March 2, 2019 2:22 PM