Oracle SQL: Date Difference in Days, Months and Years

with mytab as
(
select sysdate dt1, (sysdate-951) dt2 from dual
)
select
    case when
        round(round(last_day(dt2) - dt2) + round(dt1 - trunc(dt1,'month'))) >   to_char(last_day(dt2),'dd')
    then
        round(round(last_day(dt2) - dt2) + round(dt1 - trunc(dt1,'month'))) - to_char(last_day(dt2),'dd')
    else
        round(round(last_day(dt2) - dt2) + round(dt1 - trunc(dt1,'month')))
    end days,
    mod(floor(months_between(dt1,dt2)),12) months,
    floor(months_between(dt1,dt2)/12) years
from
    mytab;


Related Posts:
- Playing With Truncate and Date
- Oracle: Some Important Date Queries
- Dates Difference in days, hours, minutes & seconds
- ORA-01830 date format picture ends before converting entire input string
- Oracle: Convert seconds to time - hh:mi:ss
- Oracle: convert time hh:mi:ss to seconds

2 comments:

  1. Good one to refer. When I passed with mytab as sysdate-1 for dt2, I got the result as DAYS = 31

    (
    select sysdate dt1, (sysdate-1) dt2 from dual
    )

    What do I need to use for getting date difference between two timestamps in seconds?

    ReplyDelete
    Replies
    1. check this link for difference in seconds
      http://nimishgarg.blogspot.in/2009/12/get-dates-difference-in-days-hours.html

      Delete