Good evening, i want to know like in sql in oracle what will be the difference between the (SYSDATE-hire_date)/12 AS MONTHS and MONTHS_BETWEEN(SYSDATE, hire_date) as both yields different result.
Thanks in advance
When you get the number of weeks:
SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS
FROM employees WHERE department_id = 90;
LAST_NAME WEEKS
------------------------- ------------
King 1481.247
Kochhar 1363.104
De Haan 1190.247
... you're dividing by seven because there are seven days in a week.
The SYSDATE-fire_date expression gives you the number of days that have elapsed between the two dates:
SELECT last_name, hire_date, SYSDATE-hire_date AS DAYS,
(SYSDATE-hire_date)/7 AS WEEKS
FROM employees WHERE department_id = 90;
LAST_NAME HIRE_DATE DAYS WEEKS
------------------------- ---------- ------------ ------------
King 1987-06-17 10368.732 1481.247
Kochhar 1989-09-21 9541.732 1363.105
De Haan 1993-01-13 8331.732 1190.247
So there are 10368 full days between 1987-06-17 and today, 2015-11-05, plus 0.732 days between midnight and the current time, 17:34. You probably aren't really interested in the fractional days so you could trunc() either SYSDATE or the result of the subtraction; and using trunc() on the result of the division by seven would give you the number of whole weeks.
You're trying to adapt this to calculates months instead of weeks, using two calculations:
SELECT last_name, (SYSDATE-hire_date)/12 AS MONTHS1,
MONTHS_BETWEEN(SYSDATE, hire_date) AS MONTHS2
FROM employees WHERE department_id = 90;
LAST_NAME MONTHS1 MONTHS2
------------------------- ------------ ------------
King 864.061 340.637
Kochhar 795.145 313.508
De Haan 694.311 273.766
The results are so different because you're mistakenly dividing by 12. There are twelve months in a year, not twelve days in a month; you'd see a similar discrepancy if you tried to calculate the number of weeks by dividing by 52 instead of 7.
A close equivalent would be to divide by 30, as @lad2025 pointed out in a comment:
SELECT last_name, (SYSDATE-hire_date)/30 AS MONTHS1,
MONTHS_BETWEEN(SYSDATE, hire_date) AS MONTHS2
FROM employees WHERE department_id = 90;
LAST_NAME MONTHS1 MONTHS2
------------------------- ------------ ------------
King 345.625 340.637
Kochhar 318.058 313.508
De Haan 277.725 273.766
It's still not the same as most months don't have exactly 30 days. You get quite close by dividing by 365/12, which is about 30.42:
SELECT last_name, (SYSDATE-hire_date)/(365/12) AS MONTHS1,
MONTHS_BETWEEN(SYSDATE, hire_date) AS MONTHS2
FROM employees WHERE department_id = 90;
LAST_NAME MONTHS1 MONTHS2
------------------------- ------------ ------------
King 340.890 340.637
Kochhar 313.701 313.508
De Haan 273.920 273.766
But it won't be identical (except maybe for some short periods) because the algorithm isn't the same, and this still doesn't account for leap years. It's simpler and safer to just use months_between().