105
Views
14
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: #183

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

Champion
Rank: #372

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: #183

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: #55879

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: #55879

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

Rank: #183

Gen Rag wrote:

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

But you need to be an SQL? do you want to generate in the screen and present the result? Do you want to save? Cuz if you don't need a SQL you can use the solution proposed by Nikhil Gaur and generate the result you want.

Give more context if possible, take photos, upload the oml, etc


Rank: #55879

Carlos Lessa wrote:

Gen Rag wrote:

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

But you need to be an SQL? do you want to generate in the screen and present the result? Do you want to save? Cuz if you don't need a SQL you can use the solution proposed by Nikhil Gaur and generate the result you want.

Give more context if possible, take photos, upload the oml, etc


Hi Carlos Lessa,

I just need solution to generate automatic record between two date, and i think, i can use SQL to solve that.  but it doesn't seem like the right way.

I try to use solution from Nikhil Gaur.


Thank for your help :)



mvp_badge
MVP
Rank: #0

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: #55879

Nikhil Gaur wrote:

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.

Hi Nikhil Gaur,


Yes, i just want all the date between two dates and generate automatic record in table from that.

before, i want to get between two date from sql and parsing value date from SQL result  to create record in table.

but I think,  i can use solution from you. it more simple.

Thanks for your help :)

Champion
Rank: #0

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

Rank: #55879

Rajat Agrawal wrote:

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

Hi Rajat Agrawal,

i try your query, and it's work :D



i will try your solution and Nikhil Gaur.


Thank for your help :)





mvp_badge
MVP
Rank: #0

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. 

Hello, I know that this issue has been already solved. I've already try simple method from Nikhil Gaur  and it's work fine but I have another problem. I just want to put my query from SQL into the table to look like this

from the picture if there is a record in my query from SQL it will show at the same day as it's create but if it doesn't it will show "-" instead

Right now I got the table that look like this

and this is the property of my table

this is GetDates Action

this is what inside my preparation

and this is record from SQL that I want to show in the table

I have already try many methods but It doesn't work for me so can anyone help me to fix this issue?

or maybe just make an example so I can get the idea.


EARS.oap

Rank: #247

Hi Gen,

If you have your answer, could you please mark the post with that answer as a solution. This way the question is marked solved and others can benefit from it.


Thank you.