Retrieving data from 6pm previous day till 6pm present day

Question

I have one table with column "create_date". I want to select records which are inserted after 6 pm previous day till 6pm present day.This data is to be fetched everyday w/o need to manually enter date and time. So can anyone help me to write a query to fetch records which falls under this criteria. Column data type is Date.

Thanks in advance.

Answer 1

For previous day 6PM, you could use TRUNC(SYSDATE -1), which will trunc the time portion to 00:00:00 and then add 18 hours. Similarly, for present day ,do TRUNC(SYSDATE) and add 18 hours.

SQL> SELECT to_char(trunc(SYSDATE -1) + 18/24,'mm/dd/yyyy hh24:mi:ss')
  2  FROM dual;

TO_CHAR(TRUNC(SYSDA
-------------------
02/25/2015 18:00:00

SQL>

So, trunc(SYSDATE -1) + 18/24 gives you yesterday's date as 6PM.

Test case:

SQL> WITH DATA AS
  2    ( SELECT 'text' col, SYSDATE create_date FROM dual
  3    )
  4  SELECT *
  5  FROM DATA
  6  WHERE create_date
  7  BETWEEN (TRUNC(sysdate -1) + 18/24) AND (TRUNC(sysdate) + 18/24)
  8  /

COL  CREATE_DA
---- ---------
text 26-FEB-15

SQL>