'Between...And' converts text value to date type value : BETWEEN AND « Select Query « Oracle PL / SQL






'Between...And' converts text value to date type value

   
SQL>
SQL> CREATE TABLE product (
  2       product_name     VARCHAR2(25),
  3       product_price    NUMBER(4,2),
  4       quantity_on_hand NUMBER(5,0),
  5       last_stock_date  DATE);

Table created.

SQL>
SQL> INSERT INTO product VALUES ('Small Widget', 99, 1,    '15-JAN-03');

1 row created.

SQL> INSERT INTO product VALUES ('Wodget',       75, 1000, '15-JAN-02');

1 row created.

SQL> INSERT INTO product VALUES ('Product 1',    50, 100,  '15-JAN-03');

1 row created.

SQL> INSERT INTO product VALUES ('Product 2',    25, 10000, null);

1 row created.

SQL>
SQL>
SQL> SELECT * FROM product WHERE  last_stock_date BETWEEN '01-JAN-03' and '31-DEC-03';

PRODUCT_NAME              PRODUCT_PRICE QUANTITY_ON_HAND LAST_STOC
------------------------- ------------- ---------------- ---------
Small Widget                         99                1 15-JAN-03
Product 1                            50              100 15-JAN-03

2 rows selected.

SQL>
SQL>
SQL> DROP TABLE product;

Table dropped.

   
    
  








Related examples in the same category

1.Using the BETWEEN ... AND operator for Number
2.Exclude a range with NOT operator: NOT BETWEEN AND
3.Between for Date data type
4.Using the BETWEEN Operator for integer
5.BETWEEN (SYSDATE-30) AND SYSDATE
6.BETWEEN operator checks whether a variable value is between two values of the same datatype.
7.Performing Range Tests with between ... and
8.Performing Range Tests, not between ... and
9.All flower product with price range between 10 and 25