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

I believe the SQL-92 standard supports subtracting two dates with the '-' operator.

Answer 2

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

Answer 3

SQL 92 supports the following syntax:

t.date_1 - t.date_2

The EXTRACT function is also ANSI, but it isn't supported on SQL Server. Example:

ABS(EXTRACT(DAY FROM t.date_1) - EXTRACT(DAY FROM t.date_2)

Wrapping the calculation in an absolute value function ensures the value will come out as positive, even if a smaller date is the first date.

EXTRACT is supported on:

• Oracle 9i+
• MySQL
• Postgres