Oracle PL/SQL/Date Timezone/ADD MONTHS
Содержание
Adding one month to SYSDATE
SQL>
SQL>
SQL> -- Adding one month to SYSDATE.
SQL> SELECT ADD_MONTHS(TO_DATE("31-MAR-97"),1) from DUAL;
ADD_MONTH
---------
30-APR-97
SQL>
SQL>
Adding two months to current date
SQL>
SQL>
SQL> -- Adding two months to SYSDATE.
SQL> SELECT ADD_MONTHS(SYSDATE,2) from DUAL;
ADD_MONTH
---------
31-OCT-06
ADD_MONTHS: Schedule a number of months after the current date
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer");
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester");
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester");
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager");
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester");
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester");
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager");
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester");
1 row created.
SQL> /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
9 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> --ADD_MONTHS: Schedule a six months after the current start date:
SQL>
SQL> SELECT Start_Date, ADD_MONTHS(Start_Date, 6) AS Six_MONTHS FROM Employee;
START_DAT SIX_MONTH
--------- ---------
25-JUL-96 25-JAN-97
21-MAR-76 21-SEP-76
12-DEC-78 12-JUN-79
24-OCT-82 24-APR-83
15-JAN-84 15-JUL-84
30-JUL-87 30-JAN-88
31-DEC-90 30-JUN-91
17-SEP-96 17-MAR-97
17-SEP-96 17-MAR-97
9 rows selected.
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
Combine to_char with add_months
SQL>
SQL> create table ord(
2 order_no integer primary key
3 ,cust_no integer
4 ,order_date date not null
5 ,total_order_price number(7,2)
6 ,deliver_date date
7 ,deliver_time varchar2(7)
8 ,payment_method varchar2(2)
9 ,emp_no number(3,0)
10 ,deliver_name varchar2(35)
11 ,gift_message varchar2(100)
12 );
Table created.
SQL>
SQL>
SQL> insert into ord(order_no,cust_no,order_date,total_order_price,deliver_date,deliver_time,payment_method,emp_no,deliver_name,gift_message)
2 values(1,1,"14-Feb-2002", 23.00, "14-Feb-2002", "12 noon", "CA",1, null, "Gift for wife");
1 row created.
SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time ,payment_method ,emp_no,deliver_name ,gift_message )
2 values(2,1,"14-Feb-2003", 510.98, "14-feb-2003", "5 pm", "NY",7, "Rose Ted", "Happy Valentines Day to Mother");
1 row created.
SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(3, 2,"14-Feb-2004", 315.99, "14-feb-2004", "3 pm", "VS",2, "Ani Forest", "Happy Valentines Day to Father");
1 row created.
SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(4, 2,"14-Feb-1999", 191.95, "14-feb-1999", "2 pm", "NJ",2, "O. John", "Happy Valentines Day");
1 row created.
SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(5, 6,"4-mar-2002", 101.95, "5-mar-2002", "2:30 pm", "MO" , 2, "Cora", "Happy Birthday from John");
1 row created.
SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(6, 9,"7-apr-2003", 221.95, "7-apr-2003", "3 pm", "MA", 2, "Sake Keith", "Happy Birthday from Joe" );
1 row created.
SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(7, 9,"20-jun-2004", 315.95, "21-jun-2004", "12 noon", "BC", 2, "Jessica Li", "Happy Birthday from Jessica");
1 row created.
SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values (8, 12, "31-dec-1999", 135.95, "1-jan-2000", "12 noon", "DI", 3, "Larry", "Happy New Year from Lawrence");
1 row created.
SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values (9, 12, "26-dec-2003", 715.95, "2-jan-2004", "12 noon", "SK",7, "Did", "Happy Birthday from Nancy" );
1 row created.
SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(10, 4, sysdate-1, 119.95, sysdate+2, "6:30 pm", "VG",2, "P. Jing", "Happy Valentines Day to Jason");
1 row created.
SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(11, 2, sysdate, 310.00, sysdate+2, "3:30 pm", "DC",2, "C. Late", "Happy Birthday Day to Jack");
1 row created.
SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
2 values(12, 7, sysdate-3, 121.95, sysdate-2, "1:30 pm", "AC",2, "W. Last", "Happy Birthday Day to You");
1 row created.
SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
2 values(13, 7, sysdate, 211.95, sysdate-4, "4:30 pm", "CA",2, "J. Bond", "Thanks for hard working");
1 row created.
SQL>
SQL> select order_no, order_date from ord;
ORDER_NO ORDER_DATE
---------- --------------------
1 14-FEB-2002 00:00:00
2 14-FEB-2003 00:00:00
3 14-FEB-2004 00:00:00
4 14-FEB-1999 00:00:00
5 04-MAR-2002 00:00:00
6 07-APR-2003 00:00:00
7 20-JUN-2004 00:00:00
8 31-DEC-1999 00:00:00
9 26-DEC-2003 00:00:00
10 15-JUN-2008 18:01:15
11 16-JUN-2008 18:01:15
12 13-JUN-2008 18:01:15
13 16-JUN-2008 18:01:15
13 rows selected.
SQL>
SQL> select order_no, to_char(add_months(order_date, 12), "mm/dd/yyyy") as anniversary
2 from ord;
ORDER_NO ANNIVERSAR
---------- ----------
1 02/14/2003
2 02/14/2004
3 02/14/2005
4 02/14/2000
5 03/04/2003
6 04/07/2004
7 06/20/2005
8 12/31/2000
9 12/26/2004
10 06/15/2009
11 06/16/2009
12 06/13/2009
13 06/16/2009
13 rows selected.
SQL>
SQL> drop table ord;
Table dropped.
SQL>
SQL>
SQL> --
select add_months( sysdate, -12 ) "Last Year"
SQL>
SQL> select add_months( sysdate, -12 ) "Last Year"
2 from dual;
Last Year
---------
16-JUN-07
1 row selected.
SQL>
SQL> --
select add_months( sysdate, 12 ) "Next Year"
SQL> --
SQL>
SQL>
SQL> select add_months( sysdate, 12 ) "Next Year"
2 from dual;
Next Year
---------
16-JUN-09
1 row selected.
SQL>
SQL> --
Subtract Month from a given date
SQL>
SQL> SELECT TO_CHAR(SYSDATE, "ddMONyyyy") Today,
2 TO_CHAR(ADD_MONTHS(SYSDATE, 3), "ddMONyyyy") "+ 3 mon",
3 TO_CHAR(ADD_MONTHS(SYSDATE, -23), "ddMONyyyy") "- 23 mon"
4 FROM dual;
TODAY + 3 mon - 23 mon
--------- --------- ---------
30AUG2006 30NOV2006 30SEP2004
SQL>
SQL>