Number of days between two dates - ANSI SQL

sql date date-arithmetic ansi-sql
David Wright picture David Wright · Nov 25, 2009 · Viewed 12.8k times · Source

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

Answer must be in ANSI SQL.

Answer

Monkey Boson picture Monkey Boson · Nov 25, 2009

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!

Related questions

Comparing dates in Oracle using the decode function

I need to compare two dates using the Oracle decode function to see if one is less than or equal to the other. I found this article - http://www.techonthenet.com/oracle/functions/decode.php Which states (at the …

Read More
How to return only the Date from a SQL Server DateTime datatype

SELECT GETDATE() Returns: 2008-09-22 15:24:13.790 I want that date part without the time part: 2008-09-22 00:00:00.000 How can I get that?

Read More
Select data from date range between two dates

I have a table Named Product_Sales and it holds data like this Product_ID | Sold_by | Qty | From_date | To_date -----------+---------+-----+------------+----------- 3 | 12 | 7 | 2013-01-05 | 2013-01-07 6 | 22 | 14 | 2013-01-06 | 2013-01-10 8 | 11 | 9 | 2013-02-05 | 2013-02-11 Now …

Read More