I am trying to get the time difference between two dates in Age , months,Days, weeks, seconds, microseconds from todays date to date of birth -
with A
as (select sysdate, to_date('19-JAN-1987 11:23:59','DD:MON:YYYY HH24:MI:SS') date_of_birth from dual)
I created below script which is giving me results of required, but with out of cuorsity i am asking will the belkow render the correct results even with milli and micro seconds ?
select
date_of_birth
,trunc(MONTHS_BETWEEN(SYSDATE,to_date(date_of_birth,'DD-MON-YYYY HH24:MI:SS'))/12) Age
,trunc(MONTHS_BETWEEN(SYSDATE,to_date(date_of_birth,'DD-MON-YYYY HH24:MI:SS'))) Months
,trunc((trunc(sysdate)-trunc(to_date(date_of_birth,'DD-MON-YYYY HH24:MI:SS')))/7) weeks
,trunc(sysdate)- trunc(to_date(date_of_birth,'DD-MON-YYYY HH24:MI:SS')) days
,trunc(sysdate-to_date(date_of_birth,'DD-MON-YYYY HH24:MI:SS'))*24 hours
,trunc(sysdate-to_date(date_of_birth,'DD-MON-YYYY HH24:MI:SS'))*24*60 minutes
,trunc(sysdate-to_date(date_of_birth,'DD-MON-YYYY HH24:MI:SS'))*24*60*60 seconds
,trunc(sysdate-to_date(date_of_birth,'DD-MON-YYYY HH24:MI:SS'))*24*60*60*1000 milliseconds
from A
Thanks
A