CODEWITHSUNDEEP
oracle-database
Ben21
Ben21

Reputation: 93

Query for a Specific Time

How can i return data from 2 days ago at 11:00:00 PM to all of yesterday ending at 11:59:59 PM?

I currently have only yesterdays date query:

SELECT *
FROM table
WHERE code = '00'
AND to_char(RQST_TMSTMP, 'yyyy-mm-dd') = to_char(sysdate-1, 'yyyy-mm-dd')

Upvotes: 0

Views: 72

Answers (2)

Littlefoot
Littlefoot

Reputation: 143073

How about

select *
from table
where code = '00'
  and rqst_tmstmp >= trunc(sysdate - 2) + 11/24
  and rqst_tmstmp <= trunc(sysdate);

Here's what all those TRUNCs represent (so that you could follow what's going on):

SQL> select sysdate,                      -- today, right now
  2    trunc(sysdate) ts,                 -- today at midnight
  3    trunc(sysdate - 2) ts_2,           -- 2 days ago at midnight
  4    trunc(sysdate - 2) + 11/24 ts_2_11 -- 2 days ago at midnight + 11 hours 
  5  from dual;

SYSDATE          TS               TS_2             TS_2_11
---------------- ---------------- ---------------- ----------------
29.11.2018 17:07 29.11.2018 00:00 27.11.2018 00:00 27.11.2018 11:00

SQL>

Upvotes: 1

Rajes
Rajes

Reputation: 1

If the column is capturing hours & minutes then use, TO_CHAR(RQST_TMSTMP,'DD-MM-YY HH24:MI')

Upvotes: 0

Related Questions