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
(
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
Good one to refer. When I passed with mytab as sysdate-1 for dt2, I got the result as DAYS = 31
ReplyDelete(
select sysdate dt1, (sysdate-1) dt2 from dual
)
What do I need to use for getting date difference between two timestamps in seconds?
check this link for difference in seconds
Deletehttp://nimishgarg.blogspot.in/2009/12/get-dates-difference-in-days-hours.html
This comment has been removed by the author.
ReplyDeleteQuestion need command
ReplyDeleteDisplay the difference in joining date of employee Ashish and shreyas
Nice article.Thanks for the post.
ReplyDeleteSQL training in Pune