# How to use DATETIME functions in Oracle?

OracleSoftware & CodingProgramming

Problem:

You want to use datetime functions to extract or process datetimes.

Solution

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
FROM dual;

## Output

--
01/JAN/21

## Example

-- subtract 12 months
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.

Updated on 05-Dec-2020 06:08:36