75
Views
12
Comments
Generate days from date range
Question

I would like to run a query like

select ... as days where `date` is between '2020-04-07' and '2020-04-12'

And return data like:

days
----------
2020-04-07
2020-04-08
2020-04-09
2020-04-10
2020-04-11
2020-04-12

How can i do that in OS?

Rank: #205

Hi Gen Rag, you can use a SQL (normal query with input parameter with the dates you need) or an aggregate with the filters and input parameter with the dates you need

But if the question is how to do it, you can follow this link and you will find a few examples of the SQL BETWEEN Operator or this one about SQL Queries in OutSystems

Rank: #383

Hi, friend.

You need to do that through UI or just in aggregate?

In the aggregate filter, type:

Entity.Attribute >= TextToDate("2020-04-07") and

Entity.Attribute <= TextToDate("2020-04-12")


Best regards


Rank: #205

Here is an example of SQL query using 2 dates from the Booking application from the Web Developer Bootcamp

and here is an example with agregates

here a test selecting 2 dates


Hope I could help you

Rank: #49190

sorry if my question is unclear,
I want to generate record automatic based on date between two date range.

i have two table

1. Table Period (to declare date range)
Id     |StartDate    | EndDate
1    |2020-04-07    | 2020-04-12

2. Table Result (automatic generate record from date between '2020-04-07' and '2020-04-12', this table default null record)

PeriodId  |Date               |Description
1             |2020-04-07     |Tuesday
1             |2020-04-08     |Wednesday
1             |2020-04-09     |Thursday
1             |2020-04-10     |Friday
1             |2020-04-11     |Saturday
1             |2020-04-12     |Sunday


How can i do that in OS?

Rank: #49190

i try to use this query:

select * from
     (select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date from
     (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
     (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
     (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
     (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
     (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where gen_date between '2020-04-07' and '2016-04-12';

but error in OS (in MySQL, query no error).

mvp_badge
MVP
Rank: #114

Hi Gen,

You just want all the date between two dates and you are not getting data from any database table/ entity then may I know why you want to get them from database using SQL query?

You can do this using a simple loop in your server or client action.

Rank: #40

Hi Gen,

You want to do this from SQL then below query will help you -

DECLARE @MinDate DATE = '20200412',
        @MaxDate DATE = '20200424';
SELECT fn.cur_date,DATENAME(dw,fn.cur_date) as day_name,DATENAME(month,fn.cur_date) as month_name FROM (
SELECT  TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
        cur_date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)
FROM    sys.all_objects a
        CROSS JOIN sys.all_objects b ) fn



Regards,

Rajat Agrawal

mvp_badge
MVP
Rank: #114

Hi Gen,

Good to see that we were able to help you to solve your problem.

Please mark the answer as solution which you think was most suitable for your problem.