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

Labels: ,

5 comments:

  1. AnonymousJuly 29, 2014 at 8:30 PM

    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. Nimish GargJuly 30, 2014 at 8:50 AM

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

      Delete
  2. Shinde KalpeshSeptember 18, 2018 at 11:24 PM

    This comment has been removed by the author.

    ReplyDelete
  3. Shinde KalpeshSeptember 18, 2018 at 11:25 PM

    Question need command


    Display the difference in joining date of employee Ashish and shreyas

    ReplyDelete
  4. iteducationcentreSeptember 26, 2023 at 10:46 AM

    Nice article.Thanks for the post.
    SQL training in Pune

    ReplyDelete

Subscribe to: Post Comments (Atom)