CODEWITHSUNDEEP
sqldatedate-arithmeticansi-sql
David Wright
David Wright

Reputation: 235

Number of days between two dates - ANSI SQL

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

Answer must be in ANSI SQL.

Upvotes: 6

Views: 16569

Answers (4)

Monkey Boson
Monkey Boson

Reputation: 1292

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!

Upvotes: 8

OMG Ponies
OMG Ponies

Reputation: 332771

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

Upvotes: 0

Jonas Elfstr&#246;m
Jonas Elfstr&#246;m

Reputation: 31468

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

Upvotes: 3

Andy West
Andy West

Reputation: 12507

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

Upvotes: 1

Related Questions