MONTHS_BETWEEN function SQL

Companies like to work with schedules, think for example about equipment that needs to be checked every month. When you have a database where you track the dates when the equipment was checked you could write a query to figure out if a equipment part is due to be checked again or is overdue. When writing such a query you could use simple date calculations and trick some things to see the difference between one date and another date (for example sysdate) however there is a much more easy way to do this in an Oracle database using Oracle SQL when you make use of the MONTHS_BETWEEN function which is available.

The MONTHS_BETWEEN function shows the returns the number of months between one date and another date. For example if we have a table called service_log and we store the date of a service in the column service_date we could do something like the query below;

  SELECT
        MONTHS_BETWEEN(sysdate, service_date) as difference
  FROM 
      service_log

This will show the months between the current date (sysdate) and the last service date as shown below;

  DIFFERENCE
  ----------
  4.05244063620071684587813620071684587814 
  3 
  2.05244063620071684587813620071684587814 
  1.98792450716845878136200716845878136201 
  0.9556664426523297491039426523297491039427

When combining this with a round function you will be able to quickly build a report to show you if a equipment part is serviced on a monthly basis or not. An example using the round function is shown below;

  SELECT
        ROUND(MONTHS_BETWEEN(sysdate, service_date),2) as difference
  FROM 
      service_log