Ads by ProfitSence
Close

Date Difference in Oracle

Last Updated on Wednesday 5th Oct 2022

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.