Based on day fetch all dates - sql

Question

I have start date, end date and name of days. How can fetch all dates between those two dates of that specific days in sql?

example data:

• start_date:4/11/2018
• end_date: 5/11/2018
• days: monday, thursday

expected output: all dates between start and end date which comes on monday and thursday and store them in table

updated my present code(not working)

; WITH CTE(dt)
AS
(
      SELECT @P_FROM_DATE
      UNION ALL
      SELECT DATEADD(dw, 1, dt) FROM CTE
      WHERE dt < @P_TO_DATE
)
INSERT INTO Table_name 
(
    ID
    ,DATE_TIME
    ,STATUS
    ,CREATED_DATE
    ,CREATED_BY
)
SELECT @P_ID
       ,(SELECT  dt  FROM CTE WHERE DATENAME(dw, dt) In ('tuesday','friday',null))
       ,'NOT SENT'
       ,CAST(GETDATE() AS DATE)
       ,@USER_ID

Answer 1

Another approach for generating dates between ranges can be like following query. This will be faster compared to CTE or WHILE loop.

DECLARE @StartDate DATETIME = '2018-04-11'
DECLARE @EndDate DATETIME = '2018-05-15'

SELECT @StartDate + RN AS DATE FROM
(   
    SELECT (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)))-1 RN 
    FROM   master..[spt_values] T1
) T 
WHERE RN <= DATEDIFF(DAY,@StartDate,@EndDate)
AND DATENAME(dw,@StartDate + RN) IN('Monday','Thursday')

Note:

If the row count present in master..[spt_values] is not sufficient for the provided range, you can make a cross join with the same to get a bigger range like following.

SELECT (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)))-1 RN 
    FROM   master..[spt_values] T1
    CROSS JOIN master..[spt_values] T2

By this you will be able to generate date between a range with gap of 6436369 days.

Answer 2

You can use a recursive common table expression (CTE) to generate a list of days. With datepart(dw, ...) you can filter for specific days of the week.

An example that creates a list of Mondays and Thursdays between March 1st and today:

create table ListOfDates (dt date);

with    cte as
        (
        select  cast('2018-03-01' as date) as dt  -- First day of interval
        union all
        select  dateadd(day, 1, dt)
        from    cte
        where   dt < getdate()  -- Last day of interval
        )
insert  into ListOfDates
        (dt)
select  dt
from    cte
where   datepart(dw, dt) in (2, 5)  -- 2=Monday and 5=Thursday
option  (maxrecursion 0)

See it working at SQL Fiddle.