Oracle PL/SQL/PL SQL/Between and

"Between...and" operator converts text to date type

 
SQL>
SQL>
SQL> DECLARE
  2     lv_test_date VARCHAR2(10) := "01-FEB-99";
  3  BEGIN
  4     IF  lv_test_date BETWEEN "31-JAN-99" AND "10-FEB-99" THEN
  5        DBMS_OUTPUT.PUT_LINE("TRUE");
  6     ELSE
  7        DBMS_OUTPUT.PUT_LINE("FALSE");
  8     END IF;
  9  END;
 10  /
FALSE
PL/SQL procedure successfully completed.
SQL>

"Between...and" operator for Date type

 
SQL>
SQL>
SQL> DECLARE
  2     lv_test_date DATE := "01-FEB-99";
  3  BEGIN
  4     IF  lv_test_date BETWEEN "31-JAN-99" AND "10-FEB-99" THEN
  5        DBMS_OUTPUT.PUT_LINE("TRUE");
  6     ELSE
  7        DBMS_OUTPUT.PUT_LINE("FALSE");
  8     END IF;
  9  END;
 10  /
TRUE
PL/SQL procedure successfully completed.
SQL>

"Between...and" operator for number value

 
SQL>
SQL>
SQL> DECLARE
  2     lv_test_bln BOOLEAN;
  3  BEGIN
  4     lv_test_bln := 3 BETWEEN 1 and 10;
  5     IF lv_test_bln THEN
  6        DBMS_OUTPUT.PUT_LINE("Result: TRUE");
  7     ELSE
  8        DBMS_OUTPUT.PUT_LINE("Result: FALSE");
  9     END IF;
 10  END;
 11  /
Result: TRUE
PL/SQL procedure successfully completed.
SQL>
SQL>

"Between...and" operator for text value

 
SQL>
SQL>
SQL> BEGIN
  2    IF "C" BETWEEN "A" AND "D" THEN
  3        DBMS_OUTPUT.PUT_LINE("TRUE");
  4      ELSE
  5         DBMS_OUTPUT.PUT_LINE("FALSE");
  6      END IF;
  7  END;
  8  /
TRUE
PL/SQL procedure successfully completed.
SQL>