By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,364 Members | 1,637 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,364 IT Pros & Developers. It's quick & easy.

Calculate Difference Between Dates in SQL*Plus or PL/SQL

P: 12
How would you calculate the difference between two dates?
Oct 24 '05 #1
Share this Question
Share on Google+
1 Reply


P: 23
How would you calculate the difference between two dates?
Use following procedure... which i got from one site ....
it will help u....

Expand|Select|Wrap|Line Numbers
  1.  CREATE OR REPLACE 
  2. PROCEDURE PRINT_DATE_DIFF(p_dte1 IN DATE, p_dte2 IN DATE)
  3. IS
  4. v_diff NUMBER := 0;
  5. v_hrs NUMBER := 0;
  6. v_min NUMBER := 0;
  7. v_sec NUMBER := 0;
  8. BEGIN
  9. v_diff := ABS(p_dte2 - p_dte1);
  10. v_hrs := TRUNC(v_diff, 0)*24; -- start with days portion if any
  11. v_diff := (v_diff - TRUNC(v_diff, 0))*24; -- lop off whole days, convert
  12. --to hrs
  13. v_hrs := v_hrs + TRUNC(v_diff, 0); -- add in leftover hrs if any
  14. v_diff := (v_diff - TRUNC(v_diff, 0))*60; -- lop off hrs, convert to mins
  15. v_min := TRUNC(v_diff, 0); -- whole mins
  16. v_sec := TRUNC((v_diff - TRUNC(v_diff, 0))*60, 0); -- lop off mins,
  17. --convert to secs
  18. DBMS_OUTPUT.put_line(
  19. TO_CHAR(v_hrs) || ' HRS ' ||
  20. TO_CHAR(v_min) || ' MIN ' ||
  21. TO_CHAR(v_sec) || ' SEC');
  22. END print_date_diff;
  23.  
Dec 6 '05 #2

Post your reply

Sign in to post your reply or Sign up for a free account.