MOCKSTACKS
EN
Questions And Answers

More Tutorials









Oracle Working with Dates

Date Arithmetic


Oracle supports DATE (includes time to the nearest second) and TIMESTAMP (includes time to fractions of a second) datatypes, which allow arithmetic (addition and subtraction) natively. For example:

To get the next day:

SELECT TO_CHAR(SYSDATE + 1, 'YYYY-MM-DD') AS tomorrow FROM dual;

To get the previous day:

SELECT TO_CHAR(SYSDATE - 1, 'YYYY-MM-DD') AS yesterday FROM dual;

To add 5 days to the current date:

SELECT TO_CHAR(SYSDATE + 5, 'YYYY-MM-DD') AS five_days_from_now FROM dual;

To add 5 hours to the current date:

SELECT TO_CHAR(SYSDATE + (5/24), 'YYYY-MM-DD HH24:MI:SS') AS five_hours_from_now FROM dual;

To add 10 minutes to the current date:

SELECT TO_CHAR(SYSDATE + (10/1440), 'YYYY-MM-DD HH24:MI:SS') AS ten_mintues_from_now FROM dual;

To add 7 seconds to the current date:

SELECT TO_CHAR(SYSDATE + (7/86400), 'YYYY-MM-DD HH24:MI:SS') AS seven_seconds_from_now FROM dual;

To select rows where hire_date is 30 days ago or more:

SELECT * FROM emp WHERE hire_date < SYSDATE - 30;

To select rows where last_updated column is in the last hour:

SELECT * FROM logfile WHERE last_updated >= SYSDATE - (1/24);

Oracle also provides the built-in datatype INTERVAL which represents a duration of time (e.g. 1.5 days, 36 hours, 2 months, etc.). These can also be used with arithmetic with DATE and TIMESTAMP expressions. For example:

SELECT * FROM logfile WHERE last_updated >= SYSDATE - INTERVAL '1' HOUR;


Conclusion

In this page (written and validated by ) you learned about Oracle Working with Dates . What's Next? If you are interested in completing Oracle tutorial, your next topic will be learning about: Oracle Add months function.



Incorrect info or code snippet? We take very seriously the accuracy of the information provided on our website. We also make sure to test all snippets and examples provided for each section. If you find any incorrect information, please send us an email about the issue: mockstacks@gmail.com.


Share On:


Mockstacks was launched to help beginners learn programming languages; the site is optimized with no Ads as, Ads might slow down the performance. We also don't track any personal information; we also don't collect any kind of data unless the user provided us a corrected information. Almost all examples have been tested. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. By using Mockstacks.com, you agree to have read and accepted our terms of use, cookies and privacy policy.