Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to use DATETIME functions in Oracle?
Problem:
You want to use datetime functions to extract or process datetimes.
Solution
ADD_MONTHS()
ADD_MONTHS(x, y) returns the result of adding y months to x. If y is negative, then y months are subtracted from x
Example
-- add 12 months SELECT ADD_MONTHS('01-JAN-2020', 12) FROM dual;
Output
-- 01/JAN/21
Example
-- subtract 12 months
SELECT ADD_MONTHS('01-JAN-2020', -12)
FROM dual;Output
-- 01/JAN/21
We can provide a time and date to the ADD_MONTHS() function.
Example
SELECT ADD_MONTHS(TO_DATE('01-JAN-2020 01:01:01','DD-MON-YYYY HH24:MI:SS'), 2) FROM dual;
Output
-- 01/MAR/20
LAST_DAY()
LAST_DAY(x) returns the date of the last day of the month part of x.
Example
SELECT LAST_DAY('01-JAN-2020') FROM dual;
Output
-- 31/JAN/20
MONTHS_BETWEEN()
MONTHS_BETWEEN(x, y) returns the number of months between x and y. If x occurs before y in the calendar, then the number returned by MONTHS_BETWEEN() is negative
Example
SELECT MONTHS_BETWEEN('28-JUN-2020', '01-JAN-2020') FROM dual;
Output
-- 5.87096774193548387096774193548387096774
Here in above example, the later date (28-JUN-2020) appears first, the result returned is a positive number, if we flip the dates the result is negative number.
Example
SELECT MONTHS_BETWEEN( '01-JAN-2020', '28-JUN-2020') FROM dual;
Output
-- -5.87096774193548387096774193548387096774
NEXT_DAY()
NEXT_DAY(x, day) returns the date of the next day following x; you specify day as a literal string.
Example
SELECT NEXT_DAY('01-JAN-2020', 'SATURDAY') FROM dual;
Output
-- 04/JAN/20
SYSDATE
SYSDATE returns the current datetime set in the database server’s operating system. The following example gets the current date:
Example
SELECT SYSDATE FROM dual;
Output
-- 13/NOV/20
TRUNC()
TRUNC(x [, unit]) truncates x. By default, x is truncated to the beginning of the day. If you supply an optional unit string, x is truncated to that unit; for example, MM truncates x to the first day in the month.
283 Views
