Oracle PL/SQL/Data Type/TO Date

Converting number representation to DATE format

 

SQL>
SQL> -- Converting number representation to DATE format.
SQL> SELECT TO_DATE("061167","MMDDYY") "Birthday" from DUAL;
Birthday
---------
11-JUN-67
SQL>

Converting spelled date to DATE format

 
SQL>
SQL> -- Converting spelled date to DATE format.
SQL> SELECT TO_DATE("January 15","MONTH DD") "Sample" from DUAL;
Sample
---------
15-JAN-06
SQL>

MONTHS_BETWEEN with to_date function

 
SQL>
SQL> SELECT MONTHS_BETWEEN(TO_DATE("22SEP2006","ddMONyyyy"),
  2    TO_DATE("13OCT2001","ddMONyyyy")) "Months difference"
  3  FROM dual;
Months difference
-----------------
       59.2903226
SQL>
SQL>
SQL>

TO_CHAR(TO_DATE("04-JUL-15", "DD-MON-YY"), "DD-MON-YYYY")

 
SQL>
SQL> SELECT TO_CHAR(TO_DATE("04-JUL-15", "DD-MON-YY"), "DD-MON-YYYY"),
  2         TO_CHAR(TO_DATE("04-JUL-75", "DD-MON-YY"), "DD-MON-YYYY")
  3  FROM dual;
TO_CHAR(TO_ TO_CHAR(TO_
----------- -----------
04-JUL-2015 04-JUL-2075
SQL>

TO_DATE("04-JUL-15", "DD-MON-RR"): Uses the RR format when interpreting the years 15 and 75

 
SQL>
SQL> --Uses the RR format when interpreting the years 15 and 75
SQL>
SQL> SELECT
  2    TO_CHAR(TO_DATE("04-JUL-15", "DD-MON-RR"), "DD-MON-YYYY"),
  3    TO_CHAR(TO_DATE("04-JUL-75", "DD-MON-RR"), "DD-MON-YYYY")
  4  FROM dual;
TO_CHAR(TO_ TO_CHAR(TO_
----------- -----------
04-JUL-2015 04-JUL-1975
SQL>

Use to_date function with between ... and

 
SQL> CREATE TABLE sales(
  2    product_id            NUMBER(6),
  3    cid           NUMBER,
  4    time_id               DATE,
  5    channel_id            CHAR(1),
  6    promo_id              NUMBER(6),
  7    sold         NUMBER(3),
  8    amount                NUMBER(10,2),
  9    cost                  NUMBER(10,2)
 10  );
Table created.
SQL>
SQL> select sum(amount)
  2  from sales
  3  where time_id between to_date("2006-02-01", "YYYY-MM-DD") and to_date("2006-02-28", "YYYY-MM-DD");

SUM(AMOUNT)
-----------
1 row selected.
SQL>
SQL> drop table sales;
Table dropped.