SQL temp date table generation-CodePudding

I wonder is there a way to generate a temp table containing dates but using between, because I have to use such a construction.

between Convert(datetime, '2022-01-01T00:00:00.000', 126) and Convert(datetime, '2022-03-04T23:59:59.998', 126)

I mean it should use between not StartDate,EndDate.

CodePudding user response：

An option for a getting the temp table. Not 100% it will do what's required. Hope this helps though.

 DECLARE  @start_date date = '2022-01-01',
         @end_date date = '2022-03-04'

;WITH cte AS (
SELECT @start_date as DateRet
UNION ALL
SELECT CAST(DATEADD(day,1,dateRet) as date)
FROM cte
WHERE dateret < @end_date
)

SELECT *
into #tmpDates
FROM cte

CodePudding user response：

Another option which I think performs better then a CTE

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT 0 AS I
    UNION ALL
    SELECT TOP (DATEDIFF(DAY, '20220101', '20220304')) 
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2, N N3),
Dates AS(
    SELECT DATEADD(DAY, T.I, '20220101') AS Date
    FROM Tally T)
SELECT D.Date
into #tmpDates
FROM Dates D

EDIT
I always have a calendar table in my database, so I can just join on that. This performs quite well and the queries are much easier