Oracle PL/SQL/Date Timezone/TO DATE

Combine to_char, add_months and to_date function

   
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>

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

   
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> --

to_date() and insert statement

  
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>

Use to_date in insert statement

   
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> --

Using TO_DATE Within PL/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> --