Reputation: 11
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
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
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
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