list all days between two dates

CREATE FUNCTION UDF_GetAllDaysOfMonths(@FromDate date,@ToDate date)    

RETURNS   @AllDaystbl table(AllDays date)  

AS  

BEGIN  

     

with cte as    

(    

select @FromDate as   AllDays    

union all    

select  dateadd(DAY,+1,AllDays) from cte     

     

where FORMAT(dateadd(DAY,+1,AllDays),'yyyyMMdd')<=FORMAT(@ToDate,'yyyyMMdd')    

  

)    

INSERT INTO @AllDaystbl(AllDays)  

SELECT AllDays FROM cte    

---by default  recursion run 100 time after that it give error so we add this line

option (maxrecursion 0)  

return   

  

END  

Example:-

DECLARE @FromDate date

DECLARE @ToDate date

SET @FromDate='2020-01-01'

SET @ToDate= '2020-07-15'

select * from dbo.UDF_GetAllDaysOfMonths(@FromDate,@ToDate)