oracle sql developer dealing with months

Question

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

Answer 1

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().