Interview Questions Answers.ORG
Interviewer And Interviewee Guide
Interviews
Quizzes
Home
Quizzes
Interviews DB Oracle Interviews:Concepts and ArchitectureData AccessDatabase ArchitectureDatabase ManagementDatabase Security OracleDistributed ProcessingFlexfieldForms ReportsGeneral OracleMemory ManagementMTO-SAP Financial AccountingOCIOracle AOLOracle AROracle Backup RecoveryOracle D2KOracle DatabaseOracle Database DeveloperOracle DB OptimizationOracle DBAOracle DeveloperOracle ETLOracle Forms 3.0Oracle Forms 4.0Oracle GLOracle PL-SQLOracle RMANOracle ScenariosOracle SecurityOracle SQLOracle System ArchitectureOracle Technology Network (OTN)Programmatic ConstructsRAC (Real Application Clusters)SQL Plus
Copyright © 2018. All Rights Reserved
Oracle Database Interview Question:
How To Calculate Date and Time Differences in Oracle?
Submitted by: AdministratorIf you want to know how many years, months, days and seconds are there between two dates or times, you can use the date and time interval expressions: YEAR ... TO MONTH and DAY ... TO SECOND. The tutorial exercise below gives you some good examples:
SELECT
(TO_DATE('01-MAY-2006 16:52:57','DD-MON-YYYY HH24:MI:SS')
-
TO_DATE('31-JAN-1897 09:26:50','DD-MON-YYYY HH24:MI:SS'))
YEAR(4) TO MONTH FROM DUAL;
-- 109 years and 3 months
109-3
SELECT
(TO_DATE('01-MAY-2006 16:52:57','DD-MON-YYYY HH24:MI:SS')
-
TO_DATE('31-JAN-1897 09:26:50','DD-MON-YYYY HH24:MI:SS'))
DAY(9) TO SECOND FROM DUAL;
-- 39901 days and some seconds
39901 7:26:7.0
SELECT
(TO_TIMESTAMP('01-MAY-2006 16:52:57.847',
'DD-MON-YYYY HH24:MI:SS.FF3') -
TO_TIMESTAMP('31-JAN-1897 09:26:50.124',
'DD-MON-YYYY HH24:MI:SS.FF3'))
YEAR(4) TO MONTH FROM DUAL;
-- 109 years and 3 months
109-3
SELECT
(TO_TIMESTAMP('01-MAY-2006 16:52:57.847',
'DD-MON-YYYY HH24:MI:SS.FF3') -
TO_TIMESTAMP('31-JAN-1897 09:26:50.124',
'DD-MON-YYYY HH24:MI:SS.FF3'))
DAY(9) TO SECOND
FROM DUAL;
-- 39
Submitted by: Administrator
SELECT
(TO_DATE('01-MAY-2006 16:52:57','DD-MON-YYYY HH24:MI:SS')
-
TO_DATE('31-JAN-1897 09:26:50','DD-MON-YYYY HH24:MI:SS'))
YEAR(4) TO MONTH FROM DUAL;
-- 109 years and 3 months
109-3
SELECT
(TO_DATE('01-MAY-2006 16:52:57','DD-MON-YYYY HH24:MI:SS')
-
TO_DATE('31-JAN-1897 09:26:50','DD-MON-YYYY HH24:MI:SS'))
DAY(9) TO SECOND FROM DUAL;
-- 39901 days and some seconds
39901 7:26:7.0
SELECT
(TO_TIMESTAMP('01-MAY-2006 16:52:57.847',
'DD-MON-YYYY HH24:MI:SS.FF3') -
TO_TIMESTAMP('31-JAN-1897 09:26:50.124',
'DD-MON-YYYY HH24:MI:SS.FF3'))
YEAR(4) TO MONTH FROM DUAL;
-- 109 years and 3 months
109-3
SELECT
(TO_TIMESTAMP('01-MAY-2006 16:52:57.847',
'DD-MON-YYYY HH24:MI:SS.FF3') -
TO_TIMESTAMP('31-JAN-1897 09:26:50.124',
'DD-MON-YYYY HH24:MI:SS.FF3'))
DAY(9) TO SECOND
FROM DUAL;
-- 39
Submitted by: Administrator
Copyright 2007-2024 by Interview Questions Answers .ORG All Rights Reserved.
https://InterviewQuestionsAnswers.ORG.
https://InterviewQuestionsAnswers.ORG.