How to use DATETIME functions in Oracle?

OracleSoftware & CodingProgramming

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.

raja
Published on 05-Dec-2020 06:08:36
Advertisements