How To Find in Oracle DATE Difference

# How To Find in Oracle DATE Difference

###### October 16, 2019

This online tutorial is based on examples to make it easier to follow. The Oracle Date type can be used to do subtraction, but the returning number will be given in days, as you see on the next example the returning number is integer:

```SELECT (SYSDATE+1) - SYSDATE AS Days
FROM DUAL;```

When you are doing subtraction with the dates the Oracle sql will return a number in days. To turn the returned number in hours we need to multiply with 24 hours. The next Oracle date diff example we will do it – turning the output in hours.

```SELECT ((SYSDATE+1) - SYSDATE) * 24 AS Hours
FROM DUAL;```

To change the result in minutes you will need to multiply the last returned number with 60 minutes. The following Oracle DBA oracle sql query will return the datediff oracle in minutes by multiply the result with 60 minutes.

```SELECT ((SYSDATE+1) - SYSDATE) * 24 * 60 AS Minutes
FROM DUAL;```

The following Oracle DBA example with Oracle date diff will return the date result in seconds (multiply the result with 60 seconds).

```SELECT ((SYSDATE+1) - SYSDATE) * 24 * 60 * 60 AS Seconds
FROM DUAL;```

To finding out the difference in months we will use the function MONTHS_BETWEEN that gives us the final result.

```SELECT TRUNC(MONTHS_BETWEEN(SYSDATE,(SYSDATE-60))) AS Months
FROM DUAL;```

To find out date difference in years you would need to convert the number of days in an amount of month. Once you got a total number of month convert it to years by dividing it with 12 months. See the example below:

```SELECT TRUNC( MONTHS_BETWEEN(SYSDATE,(SYSDATE-600)) /12 ) AS Years
FROM DUAL;```

Now to finding out the difference in years, months and days together will make the query more complicated. See the query below:

```SELECT TRUNC(MONTHS_BETWEEN(SYSDATE,(SYSDATE-600))/12) AS Years,
MOD(TRUNC(MONTHS_BETWEEN(SYSDATE,(SYSDATE-600))),12) AS Months,