CODEWITHSUNDEEP
oracle-databasedatetime
SeT
SeT

Reputation: 224

SQL Oracle - show data only for 15th day before today

I'm trying to get the data but only for one day - 15th day before today. It needs to be dynamic, so for example when I run a query today, I should get the results for 11th June, tomorrow for 12th June etc...

I use the below line to get the data for the previous day:

bo.status_dt BETWEEN TRUNC(SYSDATE -1) AND TRUNC(SYSDATE - 1/86400)

I tried to modify my statement as follows but no luck:

bo.STATUS_DT > TRUNC(SYSDATE -15) and bo.STATUS_DT < SYSDATE +14

or

bo.status_dt = TRUNC(SYSDATE - 15)

I'm quite new to Oracle and not 100% sure how to modify my statement to get the required data. Cheers in advance.

Upvotes: 0

Views: 1149

Answers (2)

Alex Poole
Alex Poole

Reputation: 191235

Your attempt with

bo.STATUS_DT > TRUNC(SYSDATE -15) and bo.STATUS_DT < SYSDATE +14

should get more data than you want:

select sysdate, TRUNC(SYSDATE - 15), SYSDATE + 14
from dual;

SYSDATE             TRUNC(SYSDATE-15)   SYSDATE+14         
------------------- ------------------- -------------------
2020-06-26 11:41:29 2020-06-11 00:00:00 2020-07-10 11:41:29

So you'll get everything after (but not including) 2020-06-11 00:00:00, and before 2020-07-10 11:39:33.

You probably want:

bo.STATUS_DT >= TRUNC(SYSDATE -15) and bo.STATUS_DT < TRUNC(SYSDATE -14)

i.e. minus fourteen days; and using >= instead of > to include exactly midnight:

select sysdate, TRUNC(SYSDATE - 15), TRUNC(SYSDATE - 14)
from dual;

SYSDATE             TRUNC(SYSDATE-15)   TRUNC(SYSDATE-14)  
------------------- ------------------- -------------------
2020-06-26 11:41:29 2020-06-11 00:00:00 2020-06-12 00:00:00

So you'll get everything at or after (and including) 2020-06-11 00:00:00, and before 2020-06-12 00:00:00 - which is that complete day.

Using

bo.status_dt = TRUNC(SYSDATE - 15)

would only give you data at exactly 2020-06-11 00:00:00, with nothing from the rest of that day. Quite possible there would be no matching rows. You might be tempted, then, to do:

TRUNC(bo.status_dt) = TRUNC(SYSDATE - 15)

which would indeed cover the whole day; but applying the trunc (or any) function to the table column value will prevent a normal index on that column being used. You could have a function-based index, but if it's already indexed then there's no benefit to doing that instead of using the full-day range.

Upvotes: 3

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

DATE values in Oracle always include the time part, even when it is not displayed by default.

Try

SELECT 
   TO_CHAR(bo.status_dt, 'YYYY-MM-DD HH24:MI:SS'), 
   TO_CHAR(TRUNC(SYSDATE -1), 'YYYY-MM-DD HH24:MI:SS'),
   TO_CHAR(TRUNC(SYSDATE - 1/86400), 'YYYY-MM-DD HH24:MI:SS'),
   TO_CHAR(TRUNC(SYSDATE -15), 'YYYY-MM-DD HH24:MI:SS'),
   TO_CHAR(SYSDATE +14, 'YYYY-MM-DD HH24:MI:SS')

then it should be clear where the mistake is. Since you don't provide any sample data it is difficult to offer a ready-to-use solution.

Upvotes: 2

Related Questions