Date Difference in Oracle

Last Updated On Sunday 17th Oct 2021

Date Difference in Oracle

  • In Oracle, you can simply subtract two dates and get the difference in days.
  • Also note that unlike SQL Server or MySQL, in Oracle you cannot perform a select statement without a from clause.

DateDiff in Oracle

The DUAL table is a special one-row, one-column table present by default in Oracle and other database installations.

	SELECT TO_DATE('31-AUG-2021','DD-MON-YYYY') + TO_YMINTERVAL('0-1')
  FROM DUAL;

SELECT TO_DATE('29-SEP-2021','DD-MON-YYYY') + TO_YMINTERVAL('1-0')
  FROM DUAL;
	

Oracle DateDiff

	SELECT TO_DATE('2021-11-02', 'YYYY-MM-DD') -  
       TO_DATE('2021-11-01', 'YYYY-MM-DD') AS DateDiff
FROM   dual
	

Oracle SQL DateDiff

Just subtract the two dates:

	select date '2021-01-02' - date '2021-01-01' as dateDiff from dual;
	

The result will be the difference in days.