Use Date to cast string to date type : Cast « Conversion Functions « Oracle PL/SQL Tutorial






  1. Oracle PL/SQL Tutorial
  2. Conversion Functions
  3. Cast
SQL>
SQL> CREATE TABLE price_avg
  2  (product  VARCHAR(20)
  3  ,whn      DATE
  4  ,price    DECIMAL(10,2)
  5  ,PRIMARY KEY (product,whn)
  6  );

Table created.

SQL> INSERT INTO price_avg VALUES ('Product A',DATE '2006-05-19', 10);

1 row created.

SQL> INSERT INTO price_avg VALUES ('Product A',DATE '2006-05-20', 10);

1 row created.

SQL> INSERT INTO price_avg VALUES ('Product A',DATE '2006-05-21', 10.5);

1 row created.

SQL> INSERT INTO price_avg VALUES ('Product A',DATE '2006-05-22', 10.5);

1 row created.

SQL>
SQL>
SQL> CREATE TABLE price_delta
  2  (product  VARCHAR(20)
  3  ,whn      DATE
  4  ,price    DECIMAL(10,2)
  5  ,PRIMARY  KEY (product,whn)
  6  );

Table created.

SQL>
SQL> INSERT INTO price_delta VALUES ('Product A',DATE '2001-01-01', 10);

1 row created.

SQL> INSERT INTO price_delta VALUES ('Product A',DATE '2006-05-21', 10.5);

1 row created.

SQL> INSERT INTO price_delta VALUES ('Product A',DATE '2005-06-15', 9);

1 row created.

SQL> INSERT INTO price_delta VALUES ('Product B',DATE '2001-01-01', 12);

1 row created.

SQL> INSERT INTO price_delta VALUES ('Product B',DATE '2005-06-15', 13);

1 row created.

SQL>
SQL> SELECT price
  2    FROM price_avg
  3   WHERE product='Product A' AND whn=DATE '2006-05-20';

     PRICE
----------
        10

SQL>
SQL> DROP TABLE price_avg;

Table dropped.

SQL> DROP TABLE price_delta;

Table dropped.








15.10.Cast
15.10.1.cast(12.98 as number(2)) example1
15.10.2.select cast('oak' as char(10) ) example2
15.10.3.select cast(null as date ) example3
15.10.4.Cast string to date type before comparison
15.10.5.Cast null as timestamp
15.10.6.Cast table of numbers
15.10.7.Cast number to a char
15.10.8.Cast date to char
15.10.9.Cast decimal to integer
15.10.10.Cast varchar to char
15.10.11.CAST examples
15.10.12.Use Date to cast string to date type