Create DEMO DATE

 

declare @StartDT SMALLDATETIME='2017-05-01', 

@EndDT SMALLDATETIME='2017-05-30',

 @BUNIT_ID INT=10084

-- '2021-05-01','2021-05-31', 10084

;WITH DateRange(DateData) AS 

(

SELECT @StartDT as Date

UNION ALL

SELECT DATEADD(d,1,DateData)

FROM DateRange 

WHERE DateData < @EndDT

)

    SELECT [DateData],@BUNIT_ID [BunitID] INTO #DATEX

    FROM DateRange

    OPTION (MAXRECURSION 0);