Question: I have an sql query in which I need to select the records from table where the time is between 3:00 PM yesterday to 3:00 PM today *if* today's time is more than 3:00 PM.

If today's time is less than that, like if today's time is 1:00 PM. then then my query should take today's time as 1:00 PM (which should return me records).

**I need to get the time between 3:00pm yesterday to 3:00pm today if todays time is more than 3:00pm
if todays time is less than 3:00pm then get the 3:00pm yesterday to current time today**

Answer: 
The best way of handling this is to use an [IF statement](http://www.techonthenet.com/oracle/loops/if_then.php):

```
IF TO_CHAR(SYSDATE, 'HH24') >= 15 THEN 

  SELECT x.*
    FROM YOUR_TABLE x
   WHERE x.date_column BETWEEN TO_DATE(TO_CHAR(SYSDATE -1, 'YYYY-MM-DD')|| ' 15:00:00', 'YYYY-MM-DD HH24:MI:SS')
                           AND TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-DD')|| ' 15:00:00', 'YYYY-MM-DD HH24:MI:SS')

ELSE

  SELECT x.*
    FROM YOUR_TABLE x
   WHERE x.date_column BETWEEN TO_DATE(TO_CHAR(SYSDATE -1, 'YYYY-MM-DD')|| ' 15:00:00', 'YYYY-MM-DD HH24:MI:SS')
                           AND SYSDATE

END IF;

```

Conditional WHERE clauses are non-sargable.

Previously:
-----------

If I understand correctly, you want to get records within the last day. If the current time is 3 PM or later, the time should be set to 3 PM. If earlier than 3 PM, take the current time...

```
SELECT x.*
  FROM YOUR_TABLE x
  JOIN (SELECT CASE
                 WHEN TO_CHAR(SYSDATE, 'HH24') >= 15 THEN 
                  TO_DATE(TO_CHAR(SYSDATE, 'YYYY-MM-DD')|| ' 15:00:00', 'YYYY-MM-DD HH24:MI:SS')
                 ELSE SYSDATE
               END AS dt
          FROM DUAL) y ON x.date_column BETWEEN dt - 1 AND dt

```

### Note:

`dt - 1` means that 24 hours will be subtracted from the Oracle DATE.

### Reference:

* [TO\_CHAR](http://www.techonthenet.com/oracle/functions/to_char.php)
* [TO\_DATE](http://www.techonthenet.com/oracle/functions/to_date.php)
There is no need for an IF statement. This can be solved easily with simple SQL.

My table T23 has some records with dates; here is a sample with times at 3.00pm:

```
SQL> select id, some_date from t23
  2  where to_char(some_date,'HH24') = '15'
  3  /

        ID SOME_DATE
---------- ---------
        14 16-MAY-11
        38 17-MAY-11
        62 18-MAY-11
        81 19-MAY-11

SQL>

```

As the current time is before 3.00pm my query will return records from 17-MAY and 18-MAY but not the record where ID=62...

```
SQL> select to_char(sysdate, 'DD-MON-YYYY HH24:MI') as time_now
  2  from dual
  3  /

TIME_NOW
-----------------
18-MAY-2011 10:45

SQL> select id, to_char(some_date, 'DD-MON-YYYY HH24:MI') as dt
  2  from t23
  3  where some_date between trunc(sysdate-1)+(15/24)
  4                  and least( trunc(sysdate)+(15/24), sysdate)
  5  /

        ID DT
---------- -----------------
        38 17-MAY-2011 15:00
        39 17-MAY-2011 16:00
        40 17-MAY-2011 17:00
        41 17-MAY-2011 18:00
        42 17-MAY-2011 19:00
        43 17-MAY-2011 20:00
        44 17-MAY-2011 21:00
        45 17-MAY-2011 22:00
        46 17-MAY-2011 23:00
        47 18-MAY-2011 00:00
        48 18-MAY-2011 01:00
        49 18-MAY-2011 02:00
        50 18-MAY-2011 03:00
        51 18-MAY-2011 04:00
        52 18-MAY-2011 05:00
        53 18-MAY-2011 06:00
        54 18-MAY-2011 07:00
        55 18-MAY-2011 08:00
        56 18-MAY-2011 09:00
        57 18-MAY-2011 10:00

20 rows selected.

SQL>

```