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.

Yonyona

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!

Murphy

Answer 2

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

Wormeaten

SQL Server does not support that – Crosson

It sure seems so: SELECT GETDATE() - (GETDATE() - 5) => 1900-01-06 00:00:00.000 – Methylene

getdate() - getdate() should return 0 not 1900-01-01 according to the SQL standard – Crosson

Answer 3

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

Yurev

Answer 4

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

Protomartyr

If you EXTRACT from DATE before the subtraction, then the calculation only works if the two dates are in the same month. – Murphy

In addition to EXTRACT, MS SQL server also does not support subtract operator between two dates. "Operand data type date is invalid for subtract operator." – Lidalidah