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;

But you're comparing apples with oranges. sysdate has date + time information. The 2 dates that you are checking against only have time information. Of course that's not going to work. EDIT: Actually, it's worse: you are removing the time information from sysdate. So you are effectively comparing a date with 2 times. That doesn't make sense!!! — sstan, Jun 09 '15 at 19:12
What do you exaclty mean saying `is between 2 datas on the HH24:mi` ? Could you give some example ? — krokodilko, Jun 09 '15 at 19:24
Example my Sysdate is: 18:00 and the 2 times are 17:00 and 19:00. my sysdate is now in between the 2 dates. — Kevin, Jun 09 '15 at 19:26
@Kevin: No. Your sysdate is going to be something like 'June 9 18:00'. And because you are doing a `TRUNC` on it, then it will only be 'June 9 @ midnight'. And your 2 dates, because they don't have any date portion, when converted to date, will default to something like 'June 1st 17:00' and 'June 1st 19:00'. They are not in the same ballpark at all. Apples and oranges. — sstan, Jun 09 '15 at 19:35
@sstan what if i remove ' TRUNC' is it then still Apples and oranges. — Kevin, Jun 09 '15 at 19:44
@Kevin: Then it's apples and oranges of similar color :) But it's still apples and oranges. Sysdate will have today's date (June 9 + current time). Whereas your 2 dates (which are actually just times) will default to June 1st (1st day of the month). It will never match up. There is a serious flaw in your design. — sstan, Jun 09 '15 at 19:46
@sstan I only want to check the HH24:mi not date dd/mm/yy thats why I convert the date to "HH24:mi" — Kevin, Jun 09 '15 at 19:49
Then I'll post an answer for time-only comparison, if that's what you want. — sstan, Jun 09 '15 at 19:56

score 2 · Answer 1 · answered Jun 09 '15 at 20:32

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.

JonasR · Accepted Answer · 2015-06-10T09:22:05.120

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.

score 0 · Answer 3 · answered Jun 09 '15 at 20:02

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.

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

3 Answers3