How to query between hours and minute in Oracle?-CodePudding

I have the following query:

...
CASE WHEN TO_NUMBER(TO_CHAR(SYSDATE,'hh24')) BETWEEN 2 AND 4 THEN 0 else count(*) end....

But I'd like to change until 4:30AM only (04:31 not apply anymore). How can I do it? I now I can get with to_char(...,'hh24:mi') but it will be a char value.

Also, I have tried:

select TO_DATE(TO_CHAR(SYSDATE,'hh24:mi'),'HH24:MI') from dual;

but the date goes wrong.

I want something like it:

CASE WHEN TO_DATE(SYSDATE,'hh24:mi') BETWEEN TO_DATE('02:00','HH24:MI') AND TO_DATE('04:30','HH24:MI')

CodePudding user response：

Actually you can still do a range comparison using text hh:mm strings:

CASE WHEN TO_CHAR(SYSDATE, 'hh24') >= '02:00' AND
          TO_CHAR(SYSDATE, 'hh24') <= '04:30'
     THEN 0 ELSE COUNT(*) END

CodePudding user response：

One option is to use INTERVAL DAY TO SECOND data types:

CASE
WHEN (SYSDATE - TRUNC(SYSDATE)) DAY TO SECOND
     BETWEEN INTERVAL '02:00:00' HOUR TO SECOND
         AND INTERVAL '04:30:00' HOUR TO SECOND
THEN 0
ELSE count(*)
END

Or, you can do the same with arithmetic:

CASE
WHEN (SYSDATE - TRUNC(SYSDATE)) BETWEEN 2/24 AND 4.5/24
THEN 0
ELSE count(*)
END