What are different DATE functions in SQL?

Read this Oracle Tutorial Point article to  know what all different DATE functions are there in SQL.

ADD_MONTHS Function:

As per the below syntax, ADD_MONTHS function will return DATE d plus n Months. The argument n can be any integer.

Syntax:      ADD_MONTHS(d,n)

Examples:

SQL> SELECT SYSDATE, ADD_MONTHS(SYSDATE,3) FROM DUAL;

SQL> SELECT SAL, HIREDATE, ADD_MONTHS(HIREDATE,3) FROM EMP WHERE DNO=20;

MONTHS_BETWEEN Function:
As per the below syntax, MONTHS_BETWEEN function returns no.of Months between Date d1 and Date d2. If d1 is later than d2, the results is positive else negative. If d1 and d2 are either both last days of the Months or the same days of the months, the results is always an integer.

Syntax:    MONTHS_BETWEEN(D1,D2)

Examples:

SQL> SELECT ENO,HIREDATE,MONTHS_BETWEEN(SYSDATE,HIREDATE) FROM EMP WHERE MONTHS_BETWEEN(SYSDATE,HIREDATE)<222;

SQL> SELECT ENO,ENAME,MONTHS_BETWEEN(SYSDATE, HIREDATE) FROM EMP;

NEXT_DAY Function:
As per the below syntax, NEXT_DAY function returns the DATE of the WEEK DAY named by Char, that is later than the DATE d. The CHAR must be a day of the week in the sessions date language. DAY of the week can be full name or the abbreviation

Syntax:     NEXT_DAY(d,CHAR)

Examples:

SQL> SELECT SYSDATE,NEXT_DAY(SYSDATE,'FRI') FROM DUAL;

SQL> SELECT SAL,HIREDATE,NEXT_DAY(HIREDATE,'MONDAY') FROM EMP;

LAST_DAY Function:
As per the below syntax, LAST_DAY function returns the date of the last day of the month that contains d. Most commonly used to determine how many days are left in the current month.

Syntax:     LAST_DAY(d)

Examples:

SQL> SELECT SYSDATE TODAY, LAST_DAY(SYSDATE) LAST, LAST_DAY(SYSDATE) – SYSDATE DAYSLEFT FROM DUAL;

ROUND Function:
As per the below syntax, ROUND function returns Date rounded to the Unit specified by the format. If format is omitted, date is rounded to the nearest day.

Syntax:     ROUND(DATE,FORMAT)

Examples:

SQL> SELECT ROUND(SYSDATE,'YEAR') FROM DUAL;

SQL> SELECT ROUND(SYSDATE,'MONTH') FROM DUAL;

SQL> SELECTT ROUND(SYSDATE,'DAY') FROM DUAL;

TRUNC / Truncating Function:
As per the below syntax, it returns Date with the time portion of the day truncated to the specified unit. If format is omitted, date is truncated to the nearest day.

Syntax:     TRUNC(DATE,'FORMAT')

Examples:

SQL> SELECT ROUND(SYSDATE,'DAY'),TRUNC(SYSDATE,'DAY') FROM DUAL;

SQL> SELECT ROUND(SYSDATE,'MONTH'),TRUNC(SYSDATE,'MONTH') FROM DUAL;

SQL> SELECT ROUND(SYSDATE,'YEAR'),TRUNC(SYSDATE,'YEAR') FROM DUAL;