Collections:
Date and Time Differences in Oracle
How To Calculate Date and Time Differences in Oracle?
✍: FYIcenter.com
If 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; -- 39901 days and some fractional seconds 39901 7:26:7.723000000
⇐ Increment Dates by 1 in Oracle
2020-03-15, 1405🔥, 0💬
Popular Posts:
How To Use DATEADD() Function in SQL Server Transact-SQL? DATEADD() is a very useful function for ma...
How To Drop a Stored Procedure in Oracle? If there is an existing stored procedure and you don't wan...
How To Set Up SQL*Plus Output Format in Oracle? If you want to practice SQL statements with SQL*Plus...
How To Create a Dynamic Cursor with the DYNAMIC Option in SQL Server Transact-SQL? If the underlying...
What are binary literals supported in SQL Server Transact-SQL? Binary literals in Transact-SQL are s...