Oracle PL/SQL/Date Timezone/MONTHS BETWEEN

Combine Months_between, to_date, to_char together

   
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>

Count the months between now and hired date

    
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.

Experimenting with MONTHS_BETWEEN

  

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>

MONTHS_BETWEEN: number of months between two dates

  
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

MONTHS_BETWEEN: Returned result is a negative number of months

  
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>

MONTHS_BETWEEN(SYSDATE, last_stock_date)

   
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>

MONTHS_BETWEEN with to_date function

  
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>

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

  
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>

ROUND(MONTHS_BETWEEN(SYSDATE, last_stock_date),0)

   
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>

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

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

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

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