Oracle PL/SQL/Date Timezone/TO YMINTERVAL
Содержание
Date + to_yminterval("01-05")
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")
3 /
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")
3 /
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")
3 /
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")
3 /
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")
3 /
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")
3 /
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")
3 /
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")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DATE END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- ----------- ----------- ---------- ---------- ---------------
01 Jason Martin 1996-JUL-25 2006-JUL-25 1234.56 Toronto Programmer
02 Alison Mathews 1976-MAR-21 1986-FEB-21 6661.78 Vancouver Tester
03 James Smith 1978-DEC-12 1990-MAR-15 6544.78 Vancouver Tester
04 Celia Rice 1982-OCT-24 1999-APR-21 2344.78 Vancouver Manager
05 Robert Black 1984-JAN-15 1998-AUG-08 2334.78 Vancouver Tester
06 Linda Green 1987-JUL-30 1996-JAN-04 4322.78 New York Tester
07 David Larry 1990-DEC-31 1998-FEB-12 7897.78 New York Manager
08 James Cat 1996-SEP-17 2002-APR-15 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL> select start_date, start_date + to_yminterval("01-05") from employee;
START_DATE START_DATE+
----------- -----------
1996-JUL-25 1997-DEC-25
1976-MAR-21 1977-AUG-21
1978-DEC-12 1980-MAY-12
1982-OCT-24 1984-MAR-24
1984-JAN-15 1985-JUN-15
1987-JUL-30 1988-DEC-30
1990-DEC-31 1992-MAY-31
1996-SEP-17 1998-FEB-17
8 rows selected.
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
SQL>
SQL>
to_yminterval("00-01")
SQL> select to_yminterval("00-01") from dual;
TO_YMINTERVAL("00-01")
---------------------------------------------------------------------------
+000000000-01
SQL>
to_yminterval("01-05")
SQL> select to_yminterval("01-05") from dual;
TO_YMINTERVAL("01-05")
---------------------------------------------------------------------------
+000000001-05
SQL>
TO_YMINTERVAL(x): Converts the string x to an INTERVAL YEAR TO MONTH
SQL>
SQL> --TO_YMINTERVAL(x): Converts the string x to an INTERVAL YEAR TO MONTH.
SQL>
SQL> select to_yminterval("03-11")from dual;
TO_YMINTERVAL("03-11")
---------------------------------------------------------------------------
+000000003-11
SQL>
Use to_yminterval in PL/SQL
SQL>
SQL>
SQL> declare
2 l_hiredate timestamp := to_timestamp("1996-11-04 07:00:00","YYYY-MM-DD HH24:MI:SS");
3 l_oneyr interval year to month := to_yminterval("01-00");
4 l_18mos interval year to month := to_yminterval("01-06");
5 l_threeyrs interval year to month := to_yminterval("03-00");
6 l_fiveyrs interval year to month := to_yminterval("05-00");
7 begin
8 dbms_output.put_line("One Year: "||(l_hiredate + l_oneyr));
9 dbms_output.put_line("One + 1/2 Year: "||(l_hiredate + l_oneyr));
10 dbms_output.put_line("Three Years: "||(l_hiredate + l_threeyrs));
11 dbms_output.put_line("Five Years: "||(l_hiredate + l_fiveyrs));
12 end;
13 /
One Year: 04-NOV-97 07.00.00.000000000 AM
One + 1/2 Year: 04-NOV-97 07.00.00.000000000 AM
Three Years: 04-NOV-99 07.00.00.000000000 AM
Five Years: 04-NOV-01 07.00.00.000000000 AM
PL/SQL procedure successfully completed.
SQL>
SQL> --