Is there a function in SQL to calculate days between two dates?

Hong Jiang

unread,

Mar 28, 1996, 3:00:00 AM3/28/96

to

Hi,

I am new in SQL area. Could someone over there tell me there is a function in
SQL to calculate days between two dates? For example, how many days between
Jan. 23 1995 and Feb. 1 1996? I haven't found any function calculating days
directly. I know I could write a pices of program to implement this, but
I hope if there is such function I can just call.

Thank you very much.

Jiang Hong

markr69

unread,

Mar 28, 1996, 3:00:00 AM3/28/96

to

Just subtract the two date values.
For example:
SELECT SYSDATE - TO_DATE('03-02-1996','MM-DD-YYYY') FROM dual;
The result is fractional in days.
This example is Oracle specific. Date handling tends to vary
between database vendors. For example, Ingres also allows
subtraction, but returns a special data type called an 'Interval'.
Regards,
Mark Rosenthal

Scott Urman

unread,

Mar 29, 1996, 3:00:00 AM3/29/96

to

If you subtract two dates, it gives you the difference in days. For example:
DECLARE
d1 DATE := TO_DATE('07-JAN-96 12:00:00', 'DD-MON-YY HH24:MI:SS');
d2 DATE := TO_DATE('01-JAN-96 00:00:00', 'DD-MON-YY HH24:MI:SS');
x NUMBER;
BEGIN
x := d1 -d2; -- Assigns 7.5 to x
END;

--
Scott Urman
"The opinions expressed here are my own, and are not necessarily that of
Oracle Corporation"

David

unread,

Mar 29, 1996, 3:00:00 AM3/29/96

to

In article <4jebm8$h...@camelot.ccs.neu.edu>, ji...@ccs.neu.edu (Hong Jiang) wrote:
>Hi,
>
>I am new in SQL area. Could someone over there tell me there is a function in
>SQL to calculate days between two dates? For example, how many days between
>Jan. 23 1995 and Feb. 1 1996? I haven't found any function calculating days
>directly. I know I could write a pices of program to implement this, but
>I hope if there is such function I can just call.
>
>Thank you very much.
>
>Jiang Hong

SELECT MONTHS_BETWEEN(TO_DATE('02-02-1992', 'MM-DD-YYYY'),
TO_DATE('01-01-1992', 'MM-DD-YYYY'))

Gene Maillet

unread,

Mar 29, 1996, 3:00:00 AM3/29/96

to

ji...@ccs.neu.edu (Hong Jiang) wrote:
>Hi,
>
>I am new in SQL area. Could someone over there tell me there is a function in
>SQL to calculate days between two dates? For example, how many days between
>Jan. 23 1995 and Feb. 1 1996? I haven't found any function calculating days
>directly. I know I could write a pices of program to implement this, but
>I hope if there is such function I can just call.
>
>Thank you very much.
>
>Jiang Hong

Oracle's PL/SQL supports a full set of date operators. There are
many ways of finding the days between 2 dates. The simplest is
x := trunc(date1) - trunc(date2);

The trunc will set the hours:minutes:seconds to 12:00:00 for the
variables date1 and date2. Both these variables should be declared
as date.

Gene Maillet

Asgcorp

unread,

Mar 29, 1996, 3:00:00 AM3/29/96

to

An easy method to determine the number of days between two dates is using
the MONTHS_BETWEEN function:

select months_between(date_1, date_2) * 31 from dual;

If DATE_1 is later than DATE_2 the result is positive, else its negative.

Regards,

Ron Evans
ADVANCED SYSTEMS GROUP
ASG...@AOL.COM
Ron Evans
ASG...@AOL.COM

Geoff Ingram

unread,

Mar 29, 1996, 3:00:00 AM3/29/96

to

ji...@ccs.neu.edu (Hong Jiang) wrote:
>Hi,
>
>I am new in SQL area. Could someone over there tell me there is a function in
>SQL to calculate days between two dates? For example, how many days between
>Jan. 23 1995 and Feb. 1 1996? I haven't found any function calculating days

subtract two dates and the value is a decimal giving days between the
dates.

ie select date1-date2 from table;

>directly. I know I could write a pices of program to implement this, but
>I hope if there is such function I can just call.
>
>Thank you very much.
>
>Jiang Hong

regards Geoff Ingram
----------------------------
All my own personal opinions

EPepa

unread,

Mar 30, 1996, 3:00:00 AM3/30/96

to

I believe that you can just subtract the two dates. I don't have Oracle
right in front of me, so I can't tell you for sure. You may need to
divide the result by 60*60*24 if the result is given in seconds.