List dates calculated per month based on parameters

Question

Based on two dates received per parameter, I am trying to list all dates per month (one date per month) based on the start date and up to the end date. These dates returned have to check the day of the week of the start date and the number of the week, to replicate that date for all months.

For example, @StartDate = 2016/04/15 and @EndDate = 2016/09/01, I note that the @StartDate is on a first Friday in April, so until @EndDate would date all the first Friday of each month:

2016/05/06
2016/06/03
2016/07/01
2016/08/05

In case @StartDate = 2016/04/12 and @EndDate = 2016/09/01, I note that the @StartDate is on the second Tuesday of the month of April, then would pick up every Monday Tuesdays of each month:

2016/05/10
2016/06/14
2016/07/12
2016/08/09

In case @StartDate = 2016/04/28 and @EndDate = 2016/09/01, I note that the @StartDate is last week to Thursday in April:

2016/05/26
2016/06/30
2016/07/28
2016/08/25

In the latter case he would have to take into account the number of weeks of each month, since there are months with 4 and another with 5 weeks.

Answer 1

You can use a recursive CTE to generate the date list of the period and then filter the dates that correspond to the same day of the week and the week number in the month is the same as the start date.

declare @startDate date;
declare @endDate date;

declare @weekday int;
declare @weekmonth int;

select @startDate = '2016/02/15';
select @endDate = '2016/09/13';

select @weekday = DATEPART(dw,@startDate), -- dia da semana
    @weekmonth = DATEPART(day,@startDate)/7 + 1; -- número da semana no mês


;with dateRange as
(
  select dt = dateadd(dd, 1, @startDate)
  where dateadd(dd, 1, @startDate) < @endDate
  union all
  select dateadd(dd, 1, dt)
  from dateRange
  where dateadd(dd, 1, dt) < @endDate
)
select *
from dateRange 
where DATEPART(dw,dt) = @weekday
    and (DATEPART(day,dt)-1)/7 + 1 = @weekmonth
OPTION (MAXRECURSION 0)

Answer 2

Here is the solution:

set @NumSemana = datepart(day, datediff(day, DATEADD(mm, DATEDIFF(mm,0,@StartDate), 0), @StartDate)/7 * 7)/7 + 1;
WITH    AllDays
  AS ( SELECT  @StartDate  AS [Date], DATEPART(month, @StartDate) as validMonth
       UNION ALL
       SELECT   DATEADD(week, 1, [Date]), 
                iif(DATEPART(month,DATEADD(week, 1, [Date])) < validMonth + @PeriodicityRepeat, validMonth, validMonth + @PeriodicityRepeat)
       FROM     AllDays
       WHERE    
            DATEPART(month,[Date]) <= DATEPART(month,@EndDate)
        and DATEPART(year,[Date]) <= DATEPART(year,@EndDate)
            ),
rankedDays 
  AS(     
    SELECT [Date], validMonth, 
           row_number() over ( partition by DATEPART( month, [Date]) order by [Date]) ascOrder,
           row_number() over ( partition by DATEPART( month, [Date]) order by [Date] desc) descOrder
    FROM   AllDays 
    WHERE DATEPART(month, [Date]) = validMonth
)
select [Date]
from rankedDays
where ((ascOrder = @NumSemana and @NumSemana <=4 )  
        or (descOrder = 1 and @NumSemana = 5) 
        or [Date] = @StartDate )
   and [Date] < @EndDate 
OPTION (MAXRECURSION 0)

Answer 3

You can use the DATENAME passing the weekday returns the name of the week varchar and DATEPART passing the weekday to return the week in a int

declare @StartDate datetime = 2016/01/15

SELECT DATENAME(weekday,  @StartDate)  -- Retorna o nome da semana varchar
SELECT DATEPART(weekday, @StartDate) -- Retorna a semana int 




declare @StartDate datetime = getdate();

WITH CTE_PastYearinWeeks
AS (
select
DATEADD(wk, DATEDIFF(wk,0,GETDATE()-365), 0) as DateStart
UNION ALL
SELECT DATEADD(WK,DATEPART(weekday, @StartDate),DateStart)
FROM CTE_PastYearinWeeks
WHERE DATEADD(WK,DATEPART(weekday, @StartDate),DateStart) <= GETDATE()
)
select  SUBSTRING(CONVERT(varchar, DateStart, 107),1,6) as WeekTitle
from CTE_PastYearinWeeks
OPTION (MAXRECURSION 0);