How to Initialize a table that returns all days between two dates

0

Hi

Try this, Add convert attributes a date, try_cast('attrib' as date)

select
DATEDIFF(day, start_date, end_date) AS days_between
FROM
your_table;

Edit -- I misunderstood the question , I thought you meant the number of days between dates.

Hi

Try this, Add convert attributes a date, try_cast('attrib' as date)

select
DATEDIFF(day, start_date, end_date) AS days_between
FROM
your_table;

Edit -- I misunderstood the question , I thought you meant the number of days between dates.

Hi
I'd go with creating a function for that task, and would use recursive CTE.

Function like this:

CREATE FUNCTION dbo.generate_date_range(@start_date DATE, @end_date DATE)
RETURNS @DateRange TABLE (DateValue DATE)
AS
BEGIN
WITH DateRange AS (

SELECT CAST(@start_date AS DATE) AS DateValue
UNION ALL

SELECT DATEADD(day, 1, DateValue)
FROM DateRange
WHERE DateValue < @end_date
)

INSERT INTO @DateRange(DateValue)
SELECT DateValue
FROM DateRange OPTION (MAXRECURSION 1000) -- Option could be set to 0, but I'd rather avoid unintentional infinity loop :)

RETURN;
END

Later inside webcon use it like that:
select * from generate_date_range('2024-01-01', '2024-01-10')