none
Get all THIRD mondays on a given date range RRS feed

  • 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 pmak Monday, February 25, 2019 10:31 PM
    Monday, February 25, 2019 9:51 PM

Answers

  • 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  dateadd(day,n-1,@startdate) dt from Nums  
    where dateadd(day,n-1,@startdate)<=@EndDate   
    )
    
     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 
     DateAdd(Month, DateDiff(Month, 0, dt), 0)+6+14
                 -(DATEPART (Weekday, DateAdd(Month, DateDiff(Month, 0, dt), 0))
                 +@@DateFirst+4)%7 between @startdate  and @EndDate 


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

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)
                    {
                        listBox1.Items.Add("Monday");
                        ++x;
                    }
                  
                    mc = mc + x;
                    startDate = startDate.AddDays(1);
                }
                textBox4.Text =Convert.ToString(x);
            }        



    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    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 (
    select DATEADD(week,DATEDIFF(week,0,@beg_date),0) as Monday 
    union all 
    SELECT DATEADD(day,7,Monday)as Monday from cte
    Where DATEADD(day,7,Monday)<=@end_date
    ),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
    */
    

     

    Hope it can help you.

     

    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 pmak Tuesday, February 26, 2019 5:45 PM
    • Unmarked as answer by pmak 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 pmak Tuesday, February 26, 2019 10:37 PM
    Tuesday, February 26, 2019 10:24 PM
  • Hi pmak,

    Please try following script.

     

    declare @beg_date date = '20190215'  
    declare @end_date date = '20190219' 
    
    ; with cte as (
    select DATEADD(week,DATEDIFF(week,0,dateadd(dd,-day(@beg_date)+1,@beg_date)),0) as Monday 
    union all 
    SELECT DATEADD(day,7,Monday)as Monday from cte
    Where DATEADD(day,7,Monday)<=@end_date
    ),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 pmak Wednesday, February 27, 2019 4:52 PM
    • Unmarked as answer by pmak 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
    	Select T1.ID, dateadd(dd,1,dt), Month(dateadd(dd,1,dt)), YEAR(dateadd(dd,1,dt))
    	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 SharatGupta Wednesday, February 27, 2019 9:38 AM
    • Proposed as answer by SharatGupta 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  dateadd(day,n-1,@startdate) dt from Nums  
    where dateadd(day,n-1,@startdate)<=@EndDate   
    )
    
     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 pmak Wednesday, February 27, 2019 7:14 PM
    • Unmarked as answer by pmak Wednesday, February 27, 2019 7:15 PM
    Wednesday, February 27, 2019 3:05 PM
    Moderator
  • 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 dateadd(day,n-1,@startdate) dt from Nums where dateadd(day,n-1,@startdate)<=@EndDate ) select distinct dbo.NthDayOfWeekOfMonth (year(dt),'Feb','Mon',3) from DateRange

    /*

    2012-02-20 00:00:00.000
    2013-02-18 00:00:00.000
    2014-02-17 00:00:00.000
    2015-02-16 00:00:00.000
    2016-02-15 00:00:00.000
    2017-02-20 00:00:00.000
    2018-02-19 00:00:00.000
    2019-02-18 00:00:00.000

    */


    Wednesday, February 27, 2019 3:17 PM
    Moderator
  • 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  dateadd(day,n-1,@startdate) dt from Nums  
    where dateadd(day,n-1,@startdate)<=@EndDate   
    )
    
     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 
     DateAdd(Month, DateDiff(Month, 0, dt), 0)+6+14
                 -(DATEPART (Weekday, DateAdd(Month, DateDiff(Month, 0, dt), 0))
                 +@@DateFirst+4)%7 between @startdate  and @EndDate 


    • Marked as answer by pmak Wednesday, February 27, 2019 8:09 PM
    Wednesday, February 27, 2019 7:31 PM
    Moderator
  • 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 pmak 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

    You need to review the blog for more information.

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

    Hope this helps.

    Thursday, February 28, 2019 9:56 PM
    Moderator
  • 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
    ('20190219','20190220') --added 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
    	Select T1.ID, dateadd(dd,1,dt), Month(dateadd(dd,1,dt)), YEAR(dateadd(dd,1,dt))
    	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 SharatGupta Saturday, March 2, 2019 2:23 PM
    Saturday, March 2, 2019 2:22 PM