choose from a time period oracle sql

Question

I have timestamp date format in column FIRST_DATE and i need to choose time period from a certain hour, for ex. all from 18:00 10.05.21 to 18:00 11.05.2021

the problem is that date column in timestamp format - FIRST_DATE: 10/05/2020 0:00:03,000000 TIMESTAMP(6)

so i tried to use it:

select
count(*)
from TABLE
where to_char(FIRST_DATE, 'HH24:MI')>='18:00'

so with this way i was able to limit the start period by time, but if i add date to this my conditions stop working

and to_char(FIRST_DATE, 'DD-MON-YY')>='10-MAY-21'

how can i correct my script to select all from 18:00 10.05.21 to 18:00 11.05.2021

Answer 1

Don't compare dates (or timestamps) with strings. '18:00' and '10-MAY-21' are strings. Use TO_TIMESTAMP with appropriate format mask, e.g. (lines #5 and 6):

SQL> with test (first_date) as
  2    (select to_timestamp('10/05/2020 23:00:03,000000', 'dd/mm/yyyy hh24:mi:ss,ff3') from dual)
  3  select *
  4  from test
  5  where first_date between to_timestamp('10/05/2020 18:00:00,000000', 'dd/mm/yyyy hh24:mi:ss,ff3')
  6                       and to_timestamp('11/05/2020 18:00:00,000000', 'dd/mm/yyyy hh24:mi:ss,ff3')
  7  /

FIRST_DATE
---------------------------------------------------------------------------
10.05.20 23:00:03,000000000

SQL>