Results 1 to 6 of 6
  1. #1
    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. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: days between two dates

    Simply:

    select laterDate - earlierDate as days_difference from dual;

  3. #3
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    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;
    SATHISH .

  4. #4
    Join Date
    Nov 2003
    Posts
    87

    Thumbs down 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. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    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. #6
    Join Date
    Nov 2003
    Posts
    87

    Smile 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
  •