Date Functions in SQL

Date Functions in SQL

In oracle by default date format is 'DD-MON-YY'. Oracle having following pre-defined date functions.
1. SYSDATE
2. ADD_MONTHS()
3. LAST_DAY()
4. NEXT_DAY()
5. MONTHS_BETWEEN()

1.SYSDATE: It returns the current date of the system in oracle date format.
Example:  SELECT SYSDATE FROM DUAL;
Output   :  27-jul-20.

2. ADD_MONTHS:  It is used to ADD or SUBTRACT number of months from the specified date based on the second parameter.
Syntax    : ADD_MONTHS(date, number)
Example : SELECT ADD_MONTHS(SYSDATE,-1) FROM DUAL;
Output    : 27-jun-20
add_months function is used to add or subtract the month from the specified date. In the above query, we have passed negative number so system returns the back valued date(Previous date). If we pass the positive value, Then system returns future dated value.
Example : SELECT ADD_MONTHS(SYSDATE,1) FROM DUAL;
Output    : 27-aug-20

3.NEXT_DAY: It returns next occurrence day from the specified date based on the second parameter value.
Syntax    : NEXT_DAY(DATE,'DAY')
Example : SELECT NEXT_DAY(SYSDATE,'SUNDAY') FROM DUAL;
Output    : 02-aug-20

So above query returns the next occurrence day of the specified date. We have specified day as SUNDAY so system returns the next occurrence of SUNDAY date.

4. LAST_DAY:  It returns the last date of the specified month with in the specified date.
Syntax    : LAST_DAY(date)
Example : SELECT LAST_DAY(SYSDATE) FROM DUAL;
Output    : 31-JUL-20.
So in the above query, we have passed the current date, so system retuned the last day of the current month.

5. MONTHS_BETWEEN(DATE1,DATE2):

This Function always returns number data type. It is used to returns number of months between two specified dates. While using this function date1 is must be greater than date 2, otherwise system returns negative value.

Syntax: MONTHS_BETWEEN(date1,date2).

Example :select months_between(01-jan-2020,01-jan-2019) from dual;

We can perform Arithmetic operations on dates.

1. DATE +NUMBER

2. DATE - NUMBER

3. DATE1 - DATE2

But Date1 + Date2 is not possible.