# Thread: days between two dates

1. Registered User
Join Date
Nov 2003
Posts
13

## Unanswered: days between two dates

Hi ALL

I want TO find the NUMBER OF days BETWEEN two dates.
WHEN i used datediff FUNCTION i got an error LIKE
"invalid column".
How TO overcome the above error??
IS there ANY other FUNCTION TO find the days BETWEEN two dates??

Sai koushal

i tried the below on suggestion

SELECT
TO_DAYS( laterDate ) - TO_DAYS( earlierDate ) as days_difference
FROM
dual

and now getting error like "to_days" as invalid column

Sai koushal

2. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171

## Re: days between two dates

Simply:

select laterDate - earlierDate as days_difference from dual;

3. Registered User
Join Date
Nov 2003
Location
Bangalore, INDIA
Posts
333
Hi,

TO find months between 2 dates,

months_between(x,y) -- Returns a number of months between
y and x as produced by y-x. Can
return a decimal value.

Using this U can get NUMBER OF days BETWEEN two dates

SELECT ROUND(MONTHS_BETWEEN(sysdate,'01-Nov-03')*30) FROM DUAL;

4. Registered User
Join Date
Nov 2003
Posts
87

## o/p is less??

will u verify my procedure??

declare
d1 date;
d2 date;
nn integer;
begin
select tr_date into d1 from tst
where id = 'B2';
select tr_date into d2 from tst
where id = 'B3';
nn := d2 - d1;
dbms_output.put_line(nn);
end;

here my first date is '15-NOV-03'
and second date is '20-NOV-03'.

But the o/p im getting is 4.

Is the answer correct?Why shouldnt be it 5.?

thanx.

5. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171

## Re: o/p is less??

Probably your tr_date values have a time component, and are in fact less than 4.5 days apart, so round to 4 when converted to integer. For example they may be:

15-NOV-2003 23:00:00
20-NOV-2003 05:00:00

These are 4 days and 6 hours apart, i.e. 4.25 days, which rounds to 4.

If you are not interested in times, use trunc(tr_date)

6. Registered User
Join Date
Nov 2003
Posts
87

## yes..getting

thanx.

now by using trunc() im gettinmg the required o/p.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•