Oracle SQL and compare date

pts.
Tags:
BPEL
Business Process Execution Language
Database
DB2
Oracle
Oracle 9i
SQL
Hello! I need help. I want to compare two dates and count how many days its between them. Example: 2006-09-22 and 2006-10-06 results: 14 days Hope for help. Thanks!

Answer Wiki

Thanks. We'll let you know when a new response is added.

Just subtract one date from the other and the number of days between will be calculated by Oracle. Magic!

Discuss This Question: 9  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Lukedavies
    Just subtract one date from the other and the number of days between will be calculated by Oracle. Magic!
    0 pointsBadges:
    report
  • Goodyearli
    You can use following SQL statement: select to_date('2006-10-06 ','yyyy-mm-dd') - to_date('2006-09-22 ','yyyy-mm-dd')from dual;
    0 pointsBadges:
    report
  • ColinM
    Hi Lin, It's worth googling "days_between", asktom may be useful ... http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:6904756630649 and the latest docco is at: http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm#i78291 HTH, Colin.
    10 pointsBadges:
    report
  • Johndefig
    Internally Oracle stores date/times as numbers (the units are days). Therefore simply subtracting two dates gives the number of days between them. For example: SELECT date1 - date2 as "number of days" FROM orders; Should do the trick.
    0 pointsBadges:
    report
  • Katten12
    Hi, If Oracle knows it's a date format, it can make the comparison with just an ordinary compare like this: date_to - date_from select to_date('2006-10-06', 'yyyy-mm-dd') - to_date('2006-09-22', 'yyyy-mm-dd') from dual; TO_DATE('2006-10-06','YYYY-MM-DD')-TO_DATE('2006-09-22','YYYY-MM-DD') --------------------------------------------------------------------- 14 /Maria
    0 pointsBadges:
    report
  • Dschingis
    Hello everybody, Now I have an advanced question on this topic. How can I get the days between two dates without weekends? Best regards, Philipp
    10 pointsBadges:
    report
  • Meandyou
    One way would be to get the day of the week of beginning date and day of week of ending date and for every 7 days between them subtract 2. You would have to develop a process when the original difference is < 7. You would have to develop a process for when one of the dates is itself on a week-end. Another approach to this sort thing is to have a "dates table" that includes not only days of the week (so you can find week-ends) but also holidays and other dates that might not be "business days."
    5,220 pointsBadges:
    report
  • Kccrosser
    To compute days between two dates ignoring weekends, the math is pretty simple. HOWEVER, you need to know the values use for Saturday and Sunday in your system. There are default values, but they are also configurable. Assuming that in your system, Saturday is day 7 and Sunday is day 1, the following formula will give you days between two dates, excluding weekends (pseudo-code, rather than PL/SQL, for readability):
    iTotalDay = int(toDate) - int(fromDate);  -- get the integer total days between
    iWeekDays = iTotalDay - (int((iTotalDay-1)/7)*2)   -- subtract all the included weekend days
    -- now we just need to handle the start/end days
    if DayOfWeek(fromDate) = Sunday then
       iWeekDays = iWeekDays - 1
    elseif DayOfWeek(fromDate) = Saturday then
       iWeekDays = iWeekDays - 2
    end if
    if DayOfWeek(toDate) = Sunday then
       iWeekDays = iWeekDays - 2
    elseif DayOfWeek(toDate) = Saturday then
       iWeekDays = iWeekDays - 1
    end if
    There are some special cases that can all be handled by testing the end result for a negative number - when negative, the correct answer is 0. This can occur when the period is too short and includes 1-2 weekend days. (e.g., What is the count of days between saturday and sunday, not counting weekend days?).
    if iWeekDays < 0 then
       iWeekDays = 0
    end if
    3,830 pointsBadges:
    report
  • Kccrosser
    Hi - disregard the comment about "assuming ... Saturday is day 7". I started to do this in PL/SQL, and then decided English was a better format...
    3,830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following