Home > Forum > Rules, JS, SQL > How to Initialize a table that returns all days between two dates

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

Hello there :)

I have a problem when trying to generate a table using the manual initialization via HTML Button.
It only works when I initialize the table manually.

I want to use SQL however, to automate the whole thing. I failed using many Select Statements.
What would be the ideal Select statement that would return the days between two dates, which happen to be the attributes?

Best Regards,

Igor

MVP

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')

MVP
In reply to: Maksymilian Stachowiak

I'm not sure how it works in SaaS model, although on prem db admin could create a schema called 'custom', and give permissions only to that schema. This way it should be safe enough to not destroy WEBCON objects :)

Hi,

even so this is solved I wanted to add two comments:

SaaS
You don't have privileges to modify the database. The action for executing SQL or PowerShell are not available.


Adding functions views to the database
This is something you do at your own discretion and sometimes it's necessary. For example if you need to use the row_number function you have to use a few.
If you are using this option, you may consider this approach:
https://daniels-notes.de/posts/2021/deploying-database-scripts
I haven't updated the mentioned application in a long time.


Best regards,
Daniel

MVP
In reply to: Jacek Wojnar

yes, sure :-)
add this condition:

WHERE DATEPART(dw,DateData) NOT IN (1,7)

This is just an information:

WHERE DATEPART(dw,DateData) NOT IN (1,7)

The numbers of Sunday (1) and Saturday (7) may be different depending on the localization:
https://learn.microsoft.com/en-us/sql/t-sql/functions/datefirst-transact-sql?view=sql-server-ver16

Always make sure, whether they are the same on all your SQL servers or you will into unexpected errors. I speak from experience. :)