How to Use Oracle ADD_MONTHS and CURRENT_DATE Functions in SQL Queries

ADD_MONTHS

Syntax

ADD_MONTHS(date_expression, month)

Examples

1. Add a number of months to a date

The following example adds 1 month to 29-FEB-2016:

   SELECT

   ADD_MONTHS( DATE'2016-02-29', 1 )

   FROM
dual;

   The result is:

   31-MAR-16

B)  Add a negative number of months to a date

   The following statement illustrates the effect of using a negative month

   for the ADD_MONTH() function:

   SELECT

   ADD_MONTHS( DATE'2016-03-31', -1 )

   FROM
dual;

   Here is the result:
   29-FEB-16

C)  Get the last day of the last month

The following statement returns the last day of the last month.

   SELECT

   LAST_DAY( ADD_MONTHS(SYSDATE , - 1 ) )

   FROM
   dual;

LAST_DAY

---------

31-MAY-22

CURRENT_DATE

Syntax

CURRENT_DATE

Examples

The following statement changes the default date format to a new one that includes the time data:

   ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

   To find out the session time zone, you use the SESSIONTIMEZONE function    as follows:

   SELECT
   SESSIONTIMEZONE

   FROM
  DUAL;

Currently, the session time zone is set to -07:00

To get the current date in the session time zone, you use the following statement:

   SELECT
  CURRENT_DATE

   FROM
 DUAL;

Here is the output:

   06-AUG-2017 19:43:44

First, set the session time zone to -09:00:

   ALTER SESSION SET TIME_ZONE = '-09:00';

Second, get the current date of in the session time zone:

   06-AUG-2017 17:45:33