CODEWITHSUNDEEP
sqloracle-sqldeveloper
Chris
Chris

Reputation: 53

how to get month difference in 2 columns with date value in SQL developer?

I want to get the full month difference between DT_1 and DT_2. I tried the datediff, datepart and month, but it didn't work... I use SQL developer, and it shows:

ORA-00904: "DATEPART": invalid identifier 00904. 00000 - "%s: invalid identifier"

Data :

+---------+-----------+---------------+
|   ID    | DT_1      | DT_2          |
+---------+-----------+---------------+
| C111111 | 2018/1/1  | 2018/1/1      | 
| C111112 | 2017/9/30 | 2018/10/25    | 
| C111113 | 2018/10/1 | 2018/10/31    | 
| C111114 | 2018/10/6 | 2018/12/14    | 
+---------+-----------+---------------+

Expected results :

Can anyone help? Thanks.

Upvotes: 2

Views: 298

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

You want to count the number of month boundaries between two values. That is not what months_between() does. Instead, convert the values to number of months since some point in time and take the difference.

An easy way to do that uses arithmetic:

select t.*,
       ( (extract(year from dt_2) * 12 + extract(month from dt_2)) -
         (extract(year from dt_1) * 12 + extract(month from dt_1))
       ) as month_difference
from t;

Alternatively, you can use trunc() with months_between():

select t.*,
       months_between(trunc(dt_2, 'MON'), trunc(dt_1, 'MON'))

Upvotes: 0

Sonal Borkar
Sonal Borkar

Reputation: 561

You may need to see whether DT_1 and DT_2 are date type and then use MONTHS_BETWEEN function to get the date difference in months between two dates. Like Below:

SELECT MONTHS_BETWEEN  (TO_DATE('2018/1/1','YYYY/MM/DD'),  TO_DATE('2018/1/1','YYYY/MM/DD') ) "Months"  FROM DUAL;

OR,

SELECT MONTHS_BETWEEN  (TO_DATE(DT_1,'YYYY/MM/DD'),  TO_DATE(DT_2,'YYYY/MM/DD') ) "Months"  FROM TableName;

Upvotes: 4

Related Questions