How to Calculate a Date Difference in Oracle
Last updated August 25, 2026 · By the SaturnSQL team
Subtracting one DATE from another returns a NUMBER of days, with hours and minutes as a fraction. Use MONTHS_BETWEEN for a difference in months.
SELECT ship_date - order_date AS days_to_ship
FROM orders;The fractional-days surprise
date2 - date1 does not return a whole number of days if either value has a non-midnight time component. A difference of 1.5 means one and a half days, i.e. 36 hours. This trips people up when both dates come from timestamps rather than clean midnight values, and a naive comparison against an integer (like WHERE ship_date - order_date > 2) behaves correctly but reports like '2 days' can actually be 1.9 or 2.1.
SELECT ship_date - order_date AS raw_diff,
(ship_date - order_date) * 24 AS diff_in_hours,
(ship_date - order_date) * 24 * 60 AS diff_in_minutes
FROM orders;Difference in months
MONTHS_BETWEEN(date1, date2) returns the number of months between two dates as a NUMBER, including a fractional part when the day-of-month differs. If both dates fall on the same day of the month, or both are the last day of their respective months, the result is a whole number.
SELECT MONTHS_BETWEEN(SYSDATE, hire_date) AS months_employed
FROM employees;Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free