PL/SQL Check if SYSDATE is between two DATETIMES "HH24:mi"

Question

I'm trying make a function to check if my sysdate is between 2 datas on the HH24:mi. If this is true then it needs to return 1 if its not return 0.

I tried this one but without succes: Check if current date is between two dates Oracle SQL

Here is the code I used:

create or replace FUNCTION WinkelOpen(winkelID Number)
  RETURN NUMBER
IS
  CURSOR c_tijden_t(v_temp_winkelID IN NUMBER, v_temp_dag IN VARCHAR2) IS
        SELECT * FROM Openingstijd
        WHERE winkel_id = v_temp_winkelID
        AND dag = v_temp_dag;

  TYPE a_array_days IS VARRAY(7) OF VARCHAR2(2);
    v_dagen a_array_days := a_array_days('ma', 'di', 'wo', 'do', 'vr', 'za', 'zo');    

  v_temp_suc  NUMBER;
  v_isClosed Number(1,0) := 0;


BEGIN
  FOR i IN c_tijden_t(winkelID, v_dagen(to_char (SYSDATE, 'D')-1)) LOOP

    select * INTO v_temp_suc from dual 
    WHERE trunc(sysdate) 
    BETWEEN TO_DATE(i.open, 'HH24:mi') 
    AND TO_DATE(i.gesloten, 'HH24:mi');

    --if v_temp_suc is 0 then keep looping
    --else v_isClosed = 1 break loop

    END LOOP;
    RETURN v_isClosed;
END WinkelOpen;

Answer 1

Using TRUNC on a date sets it as 00:00 on that day. I assume what you're after here is "Check if the, right now, is between X and Y". Like Mick said, the following should be good:

SELECT count(*) INTO v_temp_suc FROM dual 
WHERE to_char(sysdate, 'HH24:MI') 
BETWEEN i.open AND i.gesloten;

But you also need to beware that you are potentially looping through multiple results in the cursor. You may get any number of hits but it'll only return the result on the last one. You've commented out that pseudocode part of your cursor. But you don't forget to add

IF v_temp_suc != 0 THEN
  EXIT;
END IF;

Also - depending on what type of input you're using, such as user input, you might find that they enter the later time first and the earlier second sometimes. So headsup on that if you're experiencing weird results

Meaning: IF(2 between 1 and 3) gives TRUE, but IF(2 between 3 and 1) gives FALSE.

Answer 2

Assuming the data in i.open and i.gesloten is in format HH24:MI (and hours before 10 are zero-padded), you could use this query within your procedure:

SELECT count(*) INTO v_temp_suc FROM dual 
WHERE to_char(sysdate, 'HH24:MI') 
BETWEEN i.open AND i.gesloten;

The query will either return 0 or 1, depending on whether the current time (of the database) is within the interval.

Answer 3

According to the comments below your post, you say that you are only interested in the time portion when comparing. So in that case, you can perhaps do this:

select * INTO v_temp_suc from dual 
WHERE sysdate
BETWEEN TO_DATE(to_char(sysdate, 'YYYY-MM-DD ') || i.open, 'YYYY-MM-DD HH24:MI') 
AND TO_DATE(to_char(sysdate, 'YYYY-MM-DD ') || i.gesloten, 'YYYY-MM-DD HH24:MI');

This assumes that i.open and i.gesloten are varchars, and that they really are formatted as HH24:MI.

I'm sure there are better ways of doing this, but this should at least work.