CODEWITHSUNDEEP
oracle-databaseplsql
Shah
Shah

Reputation: 15

How to get the months differences between two dates in oracle sql

Hi team i need to get the exact month differences between two dates i am using oracle sql for my project .here i am trying get the month differences between two dates sat date1 31 AUG 2020 and date_to 01 AUG 2020.i am expecting result 0 month differences but its showing one month

here is my piece of code which i am trying to get

 DECLARE 
I_DATE DATE;
I_DATE_TO DATE;
I_COUNT_1 NUMBER(10);
BEGIN
  
  I_DATE := TO_DATE ('30/08/2020','dd/mm/yyyy');
  I_DATE_TO := TO_DATE ('01/08/2020','dd/mm/yyyy');

    SELECT MONTHS_BETWEEN
      (TO_DATE(I_DATE,'dd/mm/yyyy'),
     TO_DATE(I_DATE_TO,'dd/mm/yyyy') ) 
     into I_COUNT_1
      FROM DUAL;
      
      dbms_output.put_line  ('I_COUNT_1 = ' || I_COUNT_1);
  END;

i am getiing o/p as 1

is there any anothere way to get exact months differences

Upvotes: 0

Views: 532

Answers (1)

Littlefoot
Littlefoot

Reputation: 142720

In your case, it is about resulting variable's datatype.

True result is a decimal number (0.935...):

SQL> DECLARE
  2     i_date     DATE;
  3     i_date_to  DATE;
  4     i_count_1  DECIMAL;
  5     i_count_2  NUMBER;
  6  BEGIN
  7     i_date := TO_DATE ('30/08/2020', 'dd/mm/yyyy');
  8     i_date_to := TO_DATE ('01/08/2020', 'dd/mm/yyyy');
  9
 10     i_count_1 := MONTHS_BETWEEN (i_date, i_date_to);
 11     i_count_2 := MONTHS_BETWEEN (i_date, i_date_to);
 12
 13     DBMS_OUTPUT.put_line ('I_COUNT_1 = ' || i_count_1);
 14     DBMS_OUTPUT.put_line ('I_COUNT_2 = ' || i_count_2);
 15  END;
 16  /
I_COUNT_1 = 1
I_COUNT_2 = ,935483870967741935483870967741935483871

PL/SQL procedure successfully completed.

SQL>

Now, depending on what you really want (looks like you'd want to get 0), you can truncate the value:

 <snip>
 15
 16     DBMS_OUTPUT.put_line ('truncated i_count_2 = ' || TRUNC (i_count_2));
 17  END;
 18  /
I_COUNT_1 = 1
I_COUNT_2 = ,935483870967741935483870967741935483871
truncated i_count_2 = 0

PL/SQL procedure successfully completed.

SQL>

Upvotes: 2

Related Questions