Number of days between two dates - ANSI SQL

Question

I need a way to determine the number of days between two dates in SQL.

Answer must be in ANSI SQL.

Answer 1

ANSI SQL-92 defines DATE - DATE as returning an INTERVAL type. You are supposed to be able to extract scalars from INTERVALS using the same method as extracting them from DATEs using – appropriately enough – the EXTRACT function (4.5.3).

<extract expression> operates on a datetime or interval and returns an exact numeric value representing the value of one component of the datetime or interval.

However, this is very poorly implemented in most databases. You're probably stuck using something database-specific. DATEDIFF is pretty well implemented across different platforms.

Here's the "real" way of doing it.

SELECT EXTRACT(DAY FROM DATE '2009-01-01' - DATE '2009-05-05') FROM DUAL;

Good luck!

Answer 2

I can't remember using a RDBMS that didn't support DATE1-DATE2 and SQL 92 seems to agree.