Oracle PL/SQL - CURRENT_DATE function

In this tutorial we will explain how to use the Oracle PL/SQL CURRENT_DATE function with syntax and examples

CURRENT_DATE Function : 

Syntax : CURRENT_DATE ;

• CURRENT_DATE returns the current date in the session time zone, in a value in the Gregorian calendar of datatype DATE.
• Return type is of data type DATE.
• There are no input parameters to this function.
• The output date can be altered by changing the timezone.

In the below Example 1 the CURRENT_DATE returns the date as per the timezone of UTC-5 and in NLS_DATE_FORMAT of DD-MON-YYYY HH24:MI:SS

In Example 2 , the session is altered to a timezone of UTC-7.

EXAMPLE :1 

ALTER SESSION SET TIME_ZONE = '-5:0';

SELECT CURRENT_DATE FROM DUAL;

CURRENT_DATE
--------------------
31-MAR-2015 18:14:08

EXAMPLE :2

ALTER SESSION SET TIME_ZONE = '-7:0';

SELECT CURRENT_DATE FROM DUAL;

CURRENT_DATE
--------------------
31-MAR-2015 16:14:08

Oracle PL/SQL - MONTHS_BETWEEN function

MONTHS_BETWEEN Function : 

Syntax : MONTHS_BETWEEN ('Date1','Date2');

• In Oracle PL/SQL MONTHS_BETWEEN function returns the number of months between two dates.
• Input argument to the MONTHS_BETWEEN function should be of data type 'date'.
• Return Type of MONTHS_BETWEEN is a number.
• If Date 1 is greater than Date 2 then the return number is positive
• If Date 1 is less than Date 2 then the return number is negative
• If Date 1 and Date 2  have the same day component or are the last day of the month, the return number is a whole number,Otherwise Oracle Database calculates the fractional portion of the result based on a 31-day month and considers the difference in time components Date1 and Date2.

EXAMPLE 1 : ( Date 1 is greater than Date 2)

SELECT MONTHS_BETWEEN (TO_DATE('02-02-2015','MM-DD-YYYY'),
       TO_DATE('01-01-2015','MM-DD-YYYY') ) "Months"
    FROM DUAL;

    Months
----------
1.03225806

EXAMPLE 2 : ( Date 1 is less than Date 2)

SELECT MONTHS_BETWEEN (SYSDATE,SYSDATE+1) "Months"
    FROM DUAL;

Months
----------
-.03225806

EXAMPLE 3 : ( Date 1 and Date 2 has same day)

SELECT MONTHS_BETWEEN (TO_DATE ('2015-10-25', 'YYYY-MM-DD'), TO_DATE ('2015-03-25', 'YYYY-MM-DD')) "Months"
       FROM DUAL;

Months
----------
7

Oracle PL/SQL - LAST_DAY function

LAST_DAY Function : 

Syntax : LAST_DAY ('Date');

• In Oracle PL/SQL LAST_DAY function returns the last day of the input date argument.
• Input argument to the LAST_DAY function should be of data type 'date'.
• Return Type of LAST_DAY function is always date.

In the below Example 1 the LAST_DAY returns the last day for the month of March 2015.

In Example 2 , since it is a leap year the last day of the February month is 29th.

EXAMPLE :1 

SELECT SYSDATE,
   LAST_DAY(SYSDATE) "Month_End",
     FROM DUAL;
 
SYSDATE   MONTH_END 
--------- ---------
25-MAR-15 31-MAR-15 

EXAMPLE 2 :

SELECT LAST_DAY('20-FEB-2016') "MONTH_END"
FROM DUAL;

MONTH_END
---------------
29-FEB-16

Oracle PL/SQL - ADD_MONTHS function

ADD_MONTHS Function : 

Syntax : ADD_MONTHS (date,n); 

• Return type of ADD_MONTHS is always a date.
• n is a integer or any other value that can be converted to a integer
• date can be either in a date format or any other value that can be converted to a date format.

Example 1:

SELECT ADD_MONTHS('02-MAR-2015',3) "RESULT" FROM DUAL

RESULT
-----------
02-JUN-2015

Example 2:

SELECT ADD_MONTHS('30-JAN-2015',1) "RESULT" FROM DUAL

RESULT
-----------
28-FEB-2015

Note : If the date is last day of the month or the resulting month has less number of days than the input date, then the resulting date is always the last day of the month