Oracle PL/SQL/Date Timezone/MONTHS BETWEEN

Материал из SQL эксперт
Перейти к: навигация, поиск

Combine Months_between, to_date, to_char together

   <source lang="sql">

SQL> SQL> SELECT MONTHS_BETWEEN(TO_DATE(TO_CHAR(TO_DATE("&date1"), 

 2         "mmyyyy"), "mmyyyy"),
 3         TO_DATE(TO_CHAR(TO_DATE("&date2"), "mmyyyy"),
 4         "mmyyyy")) Diff_Date
 5  FROM   DUAL;

Enter value for date1: old 1: SELECT MONTHS_BETWEEN(TO_DATE(TO_CHAR(TO_DATE("&date1"), new 1: SELECT MONTHS_BETWEEN(TO_DATE(TO_CHAR(TO_DATE(""), Enter value for date2: old 3: TO_DATE(TO_CHAR(TO_DATE("&date2"), "mmyyyy"), new 3: TO_DATE(TO_CHAR(TO_DATE(""), "mmyyyy"), 

DIFF_DATE




                                                                                                                                     Page           1

SQL> 


 </source>


Count the months between now and hired date

   <source lang="sql">

SQL> CREATE TABLE EMP( 

 2      EMPNO NUMBER(4) NOT NULL,
 3      ENAME VARCHAR2(10),
 4      JOB VARCHAR2(9),
 5      MGR NUMBER(4),
 6      HIREDATE DATE,
 7      SAL NUMBER(7, 2),
 8      COMM NUMBER(7, 2),
 9      DEPTNO NUMBER(2)
10  );

Table created. SQL> SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> SQL> select ename, hiredate, months_between(sysdate,hiredate) 

 2         "Months" from emp;

ENAME HIREDATE Months

--------- ----------

SMITH 17-DEC-80 346.302488 ALLEN 20-FEB-81 344.205714 WARD 22-FEB-81 344.141198 JONES 02-APR-81 342.786359 MARTIN 28-SEP-81 336.94765 BLAKE 01-MAY-81 341.818617 CLARK 09-JUN-81 340.560553 SCOTT 09-DEC-82 322.560553 KING 17-NOV-81 335.302488 TURNER 08-SEP-81 337.592811 ADAMS 12-JAN-83 321.463779 ENAME HIREDATE Months

--------- ----------

JAMES 03-DEC-81 334.754101 FORD 03-DEC-81 334.754101 MILLER 23-JAN-82 333.10894 14 rows selected. SQL> SQL> drop table emp; Table dropped. 



 </source>


Experimenting with MONTHS_BETWEEN

   <source lang="sql">

SQL> SQL> -- Experimenting with MONTHS_BETWEEN. SQL> SELECT MONTHS_BETWEEN("25-DEC-97","02-JUN-97") "Fractional", MONTHS_BETWEEN("02-FEB-97","02-JUN-97") "Integer" from DUAL; Fractional Integer

----------

6.74193548 -4 SQL> 


 </source>


MONTHS_BETWEEN: number of months between two dates

   <source lang="sql">

SQL> SQL> --If you need the number of months, you need to use this function. SQL> SQL> --The syntax for the function is: SQL> SQL> --MONTHS_BETWEEN(date1, date2) SQL> SQL> SQL> SELECT MONTHS_BETWEEN("01-JAN-01","01-AUG-01") AS MONTHS_BETWEEN FROM Dual; MONTHS_BETWEEN 

           -7
          
        
   
 </source>


MONTHS_BETWEEN: Returned result is a negative number of months

   <source lang="sql">

SQL> SQL> SELECT MONTHS_BETWEEN("15-JAN-2005", "25-MAY-2005") FROM dual; MONTHS_BETWEEN("15-JAN-2005","25-MAY-2005") 

                                -4.3225806

SQL> SQL> SQL> 


 </source>


MONTHS_BETWEEN(SYSDATE, last_stock_date)

   <source lang="sql">

SQL> SQL> CREATE TABLE product ( 

 2       product_name     VARCHAR2(25),
 3       product_price    NUMBER(4,2),
 4       quantity_on_hand NUMBER(5,0),
 5       last_stock_date  DATE);

Table created. SQL> SQL> INSERT INTO product VALUES ("Small Widget", 99, 1, "15-JAN-03"); 1 row created. SQL> INSERT INTO product VALUES ("Medium Widget", 75, 1000, "15-JAN-02"); 1 row created. SQL> INSERT INTO product VALUES ("Product Number", 50, 100, "15-JAN-03"); 1 row created. SQL> INSERT INTO product VALUES ("Round Church Station", 25, 10000, null); 1 row created. SQL> SQL> SQL> SQL> SELECT product_name, 

 2         last_stock_date,
 3         MONTHS_BETWEEN(SYSDATE, last_stock_date) STOCK_MONTHS
 4  FROM   product;

PRODUCT_NAME LAST_STOC STOCK_MONTHS

--------- ------------

Small Widget 15-JAN-03 65.1540293 Medium Widget 15-JAN-02 77.1540293 Product Number 15-JAN-03 65.1540293 Round Church Station SQL> SQL> DROP TABLE product; Table dropped. SQL> 


 </source>


MONTHS_BETWEEN with to_date function

   <source lang="sql">

SQL> SQL> SELECT MONTHS_BETWEEN(TO_DATE("22SEP2006","ddMONyyyy"), 

 2    TO_DATE("13OCT2001","ddMONyyyy")) "Months difference"
 3  FROM dual;

Months difference 

      59.2903226

SQL> SQL> SQL> 


 </source>


MONTHS_BETWEEN(x, y): get the number of months between x and y.

   <source lang="sql">

SQL> SQL> SELECT MONTHS_BETWEEN("25-MAY-2005", "15-JAN-2005") FROM dual; MONTHS_BETWEEN("25-MAY-2005","15-JAN-2005") 

                                4.32258065

SQL> SQL> SQL> 


 </source>


ROUND(MONTHS_BETWEEN(SYSDATE, last_stock_date),0)

   <source lang="sql">

SQL> SQL> CREATE TABLE product ( 

 2       product_name     VARCHAR2(25),
 3       product_price    NUMBER(4,2),
 4       quantity_on_hand NUMBER(5,0),
 5       last_stock_date  DATE);

Table created. SQL> SQL> INSERT INTO product VALUES ("Small Widget", 99, 1, "15-JAN-03"); 1 row created. SQL> INSERT INTO product VALUES ("Medium Widget", 75, 1000, "15-JAN-02"); 1 row created. SQL> INSERT INTO product VALUES ("Product Number", 50, 100, "15-JAN-03"); 1 row created. SQL> INSERT INTO product VALUES ("Round Church Station", 25, 10000, null); 1 row created. SQL> SQL> SQL> SELECT product_name, 

 2         last_stock_date,
 3         ROUND(MONTHS_BETWEEN(SYSDATE, last_stock_date),0) STOCK_MONTHS
 4  FROM   product;

PRODUCT_NAME LAST_STOC STOCK_MONTHS

--------- ------------

Small Widget 15-JAN-03 65 Medium Widget 15-JAN-02 77 Product Number 15-JAN-03 65 Round Church Station SQL> SQL> DROP TABLE product; Table dropped. SQL> SQL> 


 </source>


select months_between( sysdate, date"1971-05-18" )

   <source lang="sql">

SQL> SQL> select months_between( sysdate, date"1971-05-18" ) 

 2  from dual;

MONTHS_BETWEEN(SYSDATE,DATE"1971-05-18") 

                             444.958945

1 row selected. SQL> -- 


 </source>


select months_between( sysdate, date"2001-01-01" )

   <source lang="sql">

SQL> SQL> select months_between( sysdate, date"2001-01-01" ) 

 2  from dual;

MONTHS_BETWEEN(SYSDATE,DATE"2001-01-01") 

                             89.5073324

1 row selected. SQL> SQL> -- 


 </source>
Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL/Date_Timezone/MONTHS_BETWEEN&oldid=2447»