Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2002
    Posts
    42

    Question Unanswered: Date calculation

    Hello all,

    In a table I have 2 dates storing a contract starting and ending date. I need the difference between the two in days or in months.

    In SQL Server, there is a DATEDIFF function that does exactly that. I can't find something like that with Oracle 9i.

    I need to do this in a SQL statement.

    Thanks a lot.

  2. #2
    Join Date
    May 2004
    Posts
    3

    Thumbs up

    Yes, There is date function "MONTHS_BETWEEN" Look at the following example.

    SQL> select months_between(to_date('05-26-2004','MM-DD-YYYY'),
    2 to_date('01-01-2004','MM-DD-YYYY')) "Months"
    3 from dual;

    Months
    ----------
    4.80645161

    SQL>
    I hope this will help.

    rrana.

  3. #3
    Join Date
    Jul 2002
    Posts
    42
    Thanks rrana,

    But I of course tried "days_between" and it does not exist as a function. So. What is the magic combo of functions to calculate the number of days between 2 dates?

    Thanks.

  4. #4
    Join Date
    Apr 2004
    Posts
    246
    Oracle has built in date math, so
    date1 - date2
    returns the number of days between the 2 dates.

    sysdate - trunc(sysdate)
    gives the number of days since midnight, so at 6pm the answer is .75 (days).

  5. #5
    Join Date
    Jul 2002
    Posts
    42
    I was just about to post that I figured it out. Thanks.

Posting Permissions

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