Search by data in Advanced Query

I am trying to find an intem by creation date that is a DATETIME atribute, however I have a DATE variable to compare to it.

I am running this query and I get an error, what is wrong with it?


Hi Lucas,

You can simply use "Trunc" function for compare date.

Like Trunc({Incident}.[CreatedAt])>=@StartDateIncidence


Hope this will help you

Thanks 

Rahul Sahu

Lucas,

Not related to your question but i was wondering why you use an advanced sql for that query. Best practices dictate you should use aggregates when possible and this definitely seems possible for that sql of yours.

Hi lucas,


wouldn't it be worthwhile for performance reasons to convert your Start and End Dates to datetimes and use those as parameters to the query, avoiding the need to convert the CreatedAt in every row, or am I missing something ?


Dorine

Stefano Valente wrote:

Lucas,

Not related to your question but i was wondering why you use an advanced sql for that query. Best practices dictate you should use aggregates when possible and this definitely seems possible for that sql of yours.

Hi Stefano,

I think in  both ways , user will have to compare dates and their is same problem.


Rahul Sahu wrote:

Stefano Valente wrote:

Lucas,

Not related to your question but i was wondering why you use an advanced sql for that query. Best practices dictate you should use aggregates when possible and this definitely seems possible for that sql of yours.

Hi Stefano,

I think in  both ways , user will have to compare dates and their is same problem.


You can use the convert functions to do the translation in aggregates.


Hi Lucas, 

Like Stephano pointed out, unless you are using something more complex in the conditions (like a subquery), use an aggregate. 

In the aggregate, you can use the conversion DateToDateTime to convert your variables in the filter. 

But if you really need to use a query, than use the DateToDateTime function in the logic when passing the dates to the query, as this will imprive performance and will allow you to avoid conversions inside the query, like Dorine pointed. 

In anybcase, you didn't point the error you are having, so I can't say the error is related to this. 

Cheers 

In addition to what has been said above, OutSystems stores both Dates and DateTimes as a datetime in SQL Server (I'm not sure about Oracle), so comparing, in a SQL query, a datetime to a "date" should cause any errors. Even actual dates can be compared to datetimes in SQL Server, btw (in face, you can even compare datetimes to something like '2019-10-01' and it will work). So please show us the error you are having, and tell what database you are using (SQL Server or Oracle).

lucas ferreira wrote:

I am trying to find an intem by creation date that is a DATETIME atribute, however I have a DATE variable to compare to it.

I am running this query and I get an error, what is wrong with it?


Hi, Better way to use Aggregate to avoid syntax error . Basically in you sql query you normally use join and filter , this is also available in aggregate , If you use feature other than outsystems  aggregate in that case sql is help full..