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

I have something like this and it works :-)

Oh, so it seems like inside initialization CTE are working just fine - inside business rules CTE's are a trouble, so I'm using functions as a workaround. Good to know!

It's basically the same solution, just without wrapping it inside a function :)

Hello there!

I have the following error :(

Hello there :)

I have the following error, attached.

Best Regards

Could you provide also screenshots from configuration, and Webcon version?
(webcon doesn't have great support for all parts of SQL and CTE's are one of them - that's why I'm using functions, to avoid webcon trying to parse CTE).

I'm sorry, I forgot. Here's a little workaround ;)
you have to first add a query, e.g. "SELECT NULL as datedata", next configure column mapping and save configuration.

Then you can replace the query and it works (you just have to cancel the warning)

yes - function may be a better solution but sometimes it is not possible to modify/add anything to the database :/

I did all that, received the same info, saved and failed :(

what error do you have?

Same one :(

Attached.

Best Regards,

Igor

I Attached steps how I did it, and query:

WITH DateRange(DateData) AS
(
SELECT CAST('2015-01-01' as date) as Date
UNION ALL
SELECT DATEADD(d,1,DateData)
FROM DateRange
WHERE DateData < CAST('2015-01-12' as date)
)
SELECT DateData
FROM DateRange




What version do you have?

My bad, now it works, and is simply genius, thank you so so much, now that's a solution! :)

Best Regards,

Igor Sobolewski

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

Hi. I have one more question, is it possible to return these dates excluding weekend days? :)

Best Regards,

Igor

yes, sure :-)
add this condition:

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

Than you very much!

Best Regards,

Igor

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. :)