Oracle PL/SQL/Date Timezone/TO DATE

Combine to_char, add_months and to_date function

   <source lang="sql">
  

SQL> SQL> SELECT TO_CHAR(ADD_MONTHS(TO_DATE("01-JAN-2005 19:15:26","DD-MON-YYYY HH24: MI:SS"), 2), "DD-MON-YYYY HH24:MI:SS") FROM dual; TO_CHAR(ADD_MONTHS(T

---

01-MAR-2005 19:15:26 SQL>

 </source>
   
  

TO_CHAR(ADD_MON(TO_DATE("29-FEB-2000","DD-MON-YYYY"),1),"DD-MON-YYYY")

   <source lang="sql">
  

SQL> SQL> SELECT TO_CHAR(ADD_MON(TO_DATE("29-FEB-2000","DD-MON-YYYY"),1),"DD-MON-YYYY")

 2  FROM dual;

TO_CHAR(ADD

---

29-MAR-2000 1 row selected. SQL> SQL> SQL> --

 </source>
   
  

to_date() and insert statement

   <source lang="sql">
 

SQL> SQL> create sequence s_emp_id

 2  start with 1000;

Sequence created. SQL> SQL> SQL> create table gift(

 2           gift_id                integer         primary key
 3          ,emp_id                integer
 4          ,register_date              date not null
 5          ,total_price        number(7,2)
 6          ,deliver_date           date
 7          ,deliver_time           varchar2(7)
 8          ,payment        varchar2(2)
 9          ,emp_no                 number(3,0)
10          ,deliver_name           varchar2(35)
11          ,message        varchar2(100)
12  );

Table created. SQL> SQL> insert into gift(gift_id,emp_id,register_date,total_price,deliver_date,deliver_time,payment,emp_no,deliver_name,message)values

 2                 (1,1,"14-Feb-1999", 123.12, "14-Feb-1999", "12 noon", "CA",1, null, "Happy Birthday to you");

1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time ,payment ,emp_no,deliver_name ,message)values

 2                 (2,1,"14-Feb-1999", 50.98, "14-feb-1999", "1 pm", "CA",7, "name1", "Happy Birthday");

1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values

 2                 (3, 2,"14-Feb-1999", 35.99, "14-feb-1999", "1 pm", "VS",2, "Tom", "Happy Birthday");

1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values

 2                 (4, 2,"14-Feb-1999", 19.95, "14-feb-1999", "5 pm", "CA",2, "Mary", "Happy Birthday");

1 row created. SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message)values

 2                 (5, 6,"4-mar-1999", 10.95, "5-mar-1999", "4:30 pm", "VS", 2, "Jack", "Happy Birthday");

1 row created. SQL> SQL> SQL> insert into gift (gift_id, emp_id, register_date, payment)

 2  values (s_emp_id.nextval, s_emp_id.currval,
 3    to_date("01/15/2000", "mm/dd/yyyy"), "VS");

1 row created. SQL> SQL> SQL> drop table gift; Table dropped. SQL> SQL> drop sequence s_emp_id; Sequence dropped. SQL>

 </source>
   
  

Use to_date in insert statement

   <source lang="sql">
  

SQL> SQL> SQL> create table author_log(

 2      activity clob,
 3      completed date
 4    )
 5    /

Table created. SQL> SQL> SQL> insert into author_log values (

 2      "Began SQL Functions appendix",
 3      to_date( "04-NOV-2001 08:00", "DD-MON-YYYY HH24:MI" )
 4    )
 5    /

1 row created. SQL> SQL> select * from author_log; ACTIVITY COMPLETED

---

---------

Began SQL Functions appendix 04-NOV-01 1 row selected. SQL> SQL> drop table author_log; Table dropped. SQL> SQL> --

 </source>
   
  

Using TO_DATE Within PL/SQL

   <source lang="sql">
  

SQL> SQL> DECLARE

 2       v_Convert_Date DATE;
 3  BEGIN
 4       v_Convert_Date := TO_DATE("031092","MMDDYY");
 5       DBMS_OUTPUT.PUT_LINE("The converted date is: " || v_Convert_Date);
 6  END;
 7  /

The converted date is: 10-MAR-2092 00:00:00 PL/SQL procedure successfully completed. SQL> SQL> --

 </source>