CODEWITHSUNDEEP
plsqlplsqldeveloper
DeBe
DeBe

Reputation: 11

Check if a time is between two times in PLSQL

I'm trying to check if a time is between two other times in plsql. Solutions anyone?

DECLARE
    vTime   VARCHAR2(10) DEFAULT '18:00:00';
    vFrom   VARCHAR2(10) DEFAULT '17:00:00';
    vUntil  VARCHAR2(10) DEFAULT '09:00:00';
BEGIN

    IF TO_DATE(vTime,'HH24:MI:SS') BETWEEN TO_DATE(vFrom,'HH24:MI:SS') AND TO_DATE(vUntil,'HH24:MI:SS')
    THEN
        DBMS_OUTPUT.PUT_LINE(vTime||' is between '||vFrom||' and '||vUntil);
    ELSE
        DBMS_OUTPUT.PUT_LINE(vTime||' is NOT between '||vFrom||' and '||vUntil);
    END IF;

END;

Result:

18:00:00 is NOT between 17:00:00 and 09:00:00

Upvotes: 1

Views: 1436

Answers (3)

DeBe
DeBe

Reputation: 11

This worked out for me

   FUNCTION time_withing_range(b_time  IN VARCHAR2
                               ,b_start IN VARCHAR2
                               ,b_end   IN VARCHAR2)
   RETURN BOOLEAN
   IS
     vResult BOOLEAN DEFAULT FALSE;
   BEGIN

     IF TO_DATE(b_start,'HH24:MI') < TO_DATE(b_end,'HH24:MI')
     THEN

       IF TO_DATE(b_time,'HH24:MI') >= TO_DATE(b_start,'HH24:MI')
       AND TO_DATE(b_time,'HH24:MI') <= TO_DATE(b_end,'HH24:MI')
       THEN
         vResult := TRUE;
       END IF;

     ELSE

       IF TO_DATE(b_time,'HH24:MI') >= TO_DATE(b_start,'HH24:MI')
       AND TO_DATE(b_time,'HH24:MI') <= TO_DATE('23:59','HH24:MI')
       THEN
         vResult := TRUE;
       END IF;

       IF TO_DATE(b_time,'HH24:MI') >= TO_DATE('00:00','HH24:MI')
       AND TO_DATE(b_time,'HH24:MI') <= TO_DATE(b_end,'HH24:MI')
       THEN
         vResult := TRUE;
       END IF;

     END IF;

     RETURN vResult;  

   EXCEPTION
     WHEN OTHERS
     THEN
       RAISE;
   END;

Upvotes: 0

ruudvan
ruudvan

Reputation: 1371

When comparing values using BETWEEN the first value should be smaller than the second. In here the first value is greater so it is correct that you get FALSE as the result.

If however you had 17:00:00 from today and 9:00:00 from tomorrow, your result would have been TRUE, because 18:00:00 today is actually between those.

Upvotes: 0

user4629319
user4629319

Reputation: 11

The problem is wrong because the dates are in the same day for exampl

TO_DATE(vTime,'HH24:MI:SS')  = 01/03/2015 18:00:00
TO_DATE(vFrom,'HH24:MI:SS')  = 01/03/2015 17:00:00
TO_DATE(vUntil,'HH24:MI:SS') = 01/03/2015 09:00:00

So 01/03/2015 18:00:00 is NOT BETWENN 01/03/2015 17:00:00 AND 01/03/2015 09:00:00

There is little information about the process I sugerer:

IF TO_DATE(vTime,'HH24:MI:SS') BETWEEN TO_DATE(vFrom,'HH24:MI:SS') AND (TO_DATE(vUntil,'HH24:MI:SS')+1)

Upvotes: 1

Related Questions