Subtracting the two dates and find Years,Months and Days
Calculating Years , Months, and Days from the Current date with whole values as below shown oracle query updates.
select months_between(
to_date(‘03/06/2001′,’MM/DD/YYYY’),to_date(‘11/17/1992′,’MM/DD/YYYY’))/12 as “Years” from dual;
Years
———————-
8.30376344086021505376344086021505376344
select months_between(to_date(‘03/06/2001′,’MM/DD/YYYY’), to_date(‘11/17/1992′,’MM/DD/YYYY’)) as “Months” from dual;
Months
———————-
99.64516129032258064516129032258064516129
select to_date(‘03/06/2001′,’MM/DD/YYYY’) – to_date(‘11/17/1992′,’MM/DD/YYYY’) as “Total Days” from dual;
Total Days
———————-
3031
Calculating Years , Months, and Days from the Current date with calculated values from the query updates.
select trunc(months_between(to_date(‘07/09/2009′,’MM/DD/YYYY’), to_date(‘12/12/1975′,’MM/DD/YYYY’))/12) as “Years” from dual;
Years
———————-
33
select mod(trunc(months_between(to_date(‘07/09/2009′,’MM/DD/YYYY’),to_date(‘12/12/1975′,’MM/DD/YYYY’))),12) as “Months” from dual;
Months
———————-
6
select trunc(to_date(‘07/09/2009′,’MM/DD/YYYY’) – add_months(to_date(‘12/12/1975′,’MM/DD/YYYY’),
months_between(to_date(‘07/09/2009′,’MM/DD/YYYY’), to_date(‘12/12/1975′,’MM/DD/YYYY’)))) as “Days” from dual;
Days
———————-
27
and finally complete single query to fetch the Total Years , Months and Days
select trunc(months_between(to_date(‘07/09/2009′,’MM/DD/YYYY’), to_date(‘12/12/1975′,’MM/DD/YYYY’))/12) as “Years” ,
mod(trunc(months_between(to_date(‘07/09/2009′,’MM/DD/YYYY’),to_date(‘12/12/1975′,’MM/DD/YYYY’))),12) as “Months”,
trunc(to_date(‘07/09/2009′,’MM/DD/YYYY’) – add_months(to_date(‘12/12/1975′,’MM/DD/YYYY’),
months_between(to_date(‘07/09/2009′,’MM/DD/YYYY’), to_date(‘12/12/1975′,’MM/DD/YYYY’)))) as “Days” from dual;
Years Months Days
———————- ———————- ———————-
33 6 27
1 rows selected
The other way to work out the same problem in the below :
If you want a solution which breaks the days in years and month you can use the following query. We will use a leap year date, 01/01/2000 for example, for temporary purposes. This date will provide accurate calculation for most cases.
DEFINE DateDay = 8752.44056
SELECT
TO_NUMBER(SUBSTR(A,1,4)) – 2000 years,
TO_NUMBER(SUBSTR(A,6,2)) – 01 months,
TO_NUMBER(SUBSTR(A,9,2)) – 01 days,
SUBSTR(A,12,2) hours,
SUBSTR(A,15,2) minutes,
SUBSTR(A,18,2) seconds
FROM (SELECT TO_CHAR(TO_DATE(‘20000101′,’YYYYMMDD’)
+ &DateDay,’YYYY MM DD HH24:MI:SS’) A
FROM DUAL);
YEARS MONTHS DAYS HO MI SE
———- ———- ———- — – –
23 11 17 10 34 24
The new TIMESTAMP datatype
Convert DATE datatype to TIMESTAMP datatype
CREATE TABLE date_table (
date1 DATE,
time1 TIMESTAMP,
time2 TIMESTAMP
);
INSERT INTO date_table (date1, time1, time2)
VALUES (SYSDATE,
TO_TIMESTAMP (‘17.12.1980:00:00:00′,’DD.MM.YYYY:HH24:MI:SS’),
TO_TIMESTAMP (‘03.12.2004:10:34:24′,’DD.MM.YYYY:HH24:MI:SS’)
);
COMMIT;
SELECT CAST(date1 AS TIMESTAMP) “Date” FROM date_table;
Date
—————————————————————————
03-DEC-04 11.36.45.000000 AM
Formatting of the TIMESTAMP datatype:
SELECT TO_CHAR(time1,’MM/DD/YYYY HH24:MI:SS’) “Date”
FROM date_table;
Date
——————-
12/17/1980 00:00:00
Formatting of the TIMESTAMP datatype with fractional seconds:
SELECT TO_CHAR(time1,’MM/DD/YYYY HH24:MI:SS:FF3′) “Date”
FROM date_table;
Date
—————————–
12/17/1980 00:00:00:000
Posted by raajasoft 
