Oracle PL/SQL/Date Timezone/TO YMINTERVAL
Содержание
Date + to_yminterval("01-05")
<source lang="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")
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>
</source>
to_yminterval("00-01")
<source lang="sql">
SQL> select to_yminterval("00-01") from dual; TO_YMINTERVAL("00-01")
+000000000-01 SQL>
</source>
to_yminterval("01-05")
<source lang="sql">
SQL> select to_yminterval("01-05") from dual; TO_YMINTERVAL("01-05")
+000000001-05 SQL>
</source>
TO_YMINTERVAL(x): Converts the string x to an INTERVAL YEAR TO MONTH
<source lang="sql">
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>
</source>
Use to_yminterval in PL/SQL
<source lang="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> --
</source>
