Date-Manipulation Functions in Oracle

Topic Introduction: In this tutorial, we will show the use of Date-Manipulation Functions in Oracle.

In the Oracle database system, these functions exclusively work on dates, and their output is typically in the DATE data type, with the exception of the MONTHS_BETWEEN function, which returns a numeric value instead. This information suggests that users working within the Oracle system can rely on these functions to manipulate and analyze date data in a consistent and predictable manner.

Date-Manipulation Functions

Function	Result
MONTHS_BETWEEN	Number of months between two dates
ADD_MONTHS	Add calendar months to date
NEXT_DAY	Next day of the date specified
LAST_DAY	Last day of the month
ROUND	Round date
19	87
TRUNC	Truncate date

MONTHS_BETWEEN

Purpose: Find the number of months between the date1 and date2

Structure: MONTHS_BETWEEN(date1, date2)

Example1: select MONTHS_BETWEEN ('01-DEC-23','22-JAN-22') from dual;

Result: 22.3225806451613

ADD_MONTHS

Purpose: Adds n number of calendar months to date value must be an integer and will be positive.

Structure: ADD_MONTHS(date, n)

Example1: select ADD_MONTHS ('01-MAR-23',3) from dual;

Result: 01-JUN-23

NEXT_DAY

Purpose: Find the date of the next specified day of the week ('char') on the following date.

Structure: NEXT_DAY(date, 'char')

Example1: select NEXT_DAY ('01-MAR-23','SATURDAY') from dual;

Result: 04-MAR-23

LAST_DAY

Purpose: Find the date of the last day of the month

Structure: LAST_DAY(date)

Example1: select LAST_DAY ('01-MAR-23') from dual;

Result: 31-MAR-23

ROUND

Purpose: Returns date rounded to the unit that is specified by the format model fmt.

Structure: ROUND(date[,'fmt'])

SYSDATE: Assume SYSDATE = '19-AUG-22':

Example1: select ROUND(SYSDATE,'MONTH') from dual;

Result: 01-SEP-23

Example2: select ROUND(SYSDATE,'YEAR') from dual;

Result: 01-JAN-23

TRUNC

Purpose: Returns date with the time portion of the day truncated to the unit that is specified by the format model fmt.

Structure: TRUNC(date[, 'fmt'])

SYSDATE: Assume SYSDATE = '19-AUG-22':

Example1: select TRUNC(SYSDATE,'MONTH') from dual;

Result: 01-AUG-22

Example2: select TRUNC(SYSDATE,'YEAR') from dual;

Result: 01-JAN-22