1

I'm trying to create a "dynamic" query to run a scheduled report every morning. I have a client that wish to have a report sent every morning where he gets information about closed tickets yesterday.

I have tried to search for yesterday with a query. see below:

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and upper(reportedby) = 'Personthatreported@person.com' and istask = 0 and (siteid = 'TPSELUN' or siteid = 'TPSELUO' and (actfinish between trunc(sysdate-1) and sysdate)))

I don't get an error message but the result is not correct. Is there another way of searching for actual finish from yesterday.

Tom Baeckstrom
  • 13
  • 4

1 Answers1

1

If you wish to find only yesterday's data then you need to try with

actfinish between trunc(sysdate-1) and trunc(sysdate)- INTERVAL '1' SECOND

or 

trunc(actfinish) = trunc(sysdate-1)
Popeye
  • 35,427
  • 4
  • 10
  • 31
  • 1
    Your answer is fine, but your first query has a small risk of missing something. With `between`, you will always miss something, in principle, because it only includes up to the precision you specify. On the other hand, `actfinish >= trunc(sysdate-1) and actfinish < trunc(sysdate)` will include exactly all of yesterday, to infinite precision. So, your first query will miss events recorded in the last second of the day but after the first fraction of it. The second query will perform worse because wrapping column references with function calls tend to preclude using indexes on those columns. – Preacher May 27 '20 at 16:21
  • @Preacher Considering the `actfinish` of date datatype, first query will not miss anything as `second` is the smallest unit in date datatype. – Popeye May 28 '20 at 05:27
  • @Tejash True. In practice, because the date datatype in Oracle is only to the precision of second, your query will not miss anything. But in principle, it will miss nearly a whole second. And keeping the principle in mind helps across languages and different precisions of date datatypes. – Preacher May 28 '20 at 14:10
  • What if i wanted to a "last month" search? not -30 days because every month is different in days. – Tom Baeckstrom Jun 01 '20 at 08:47
  • You need to use the `ADD_MONTHS` function. – Popeye Jun 01 '20 at 09:38