ADD_MONTHS Function - Oracle to SQL Server Migration

In Oracle, ADD_MONTHS function adds the specified number of months to a datetime value. In SQL Server, you can use DATEADD function.

Oracle:

   -- Add 3 months to the current date (February 10, 2013):
   SELECT ADD_MONTHS(SYSDATE, 3) FROM dual;
   # 10-MAY-2013

Note that the output format for DATE values depends on the NLS_DATE_FORMAT session variable.

SQL Server:

   -- Add 3 months to the current date (February 10, 2013):
   SELECT DATEADD(month, 3, GETDATE());
   # 2013-05-10 17:12:56.440

Note the different order of datetime and num_of_months parameters in ADD_MONTHS and DATEADD .

ADD_MONTHS Conversion Overview

Summary information:

Oracle SQL Server
Syntax ADD_MONTHS(datetime, num_of_months) DATEADD(month, num_of_months, datetime)
Negative Number Subtracts the specified number of months
Last Day If the resulting month has fewer days then its last day is set
Alternatives datetime + num_of_days, see also Datetime Arithmetic

Last Update: Oracle 11g Release 2 and Microsoft SQL Server 2012

Converting ADD_MONTHS from Oracle to SQL Server

In Oracle, ADD_MONTHS function adds or subtracts the specified number of months from a datetime expression (DATE or TIMESTAMP data type):

Oracle:

    -- Set default datetime format in string literals
    ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';
 
    -- Add 1 month to January 11, 2012
    SELECT ADD_MONTHS('11-JAN-2012', 1) FROM dual;
    # 11-FEB-2012 
 
    -- Subtract 1 month from January 11, 2012
    SELECT ADD_MONTHS('11-JAN-2012', -1) FROM dual;
    # 11-DEC-2011

In SQL Server, you can use DATEADD function with month, mm or m datepart parameter:

SQL Server:

    -- Add 1 month to January 11, 2012
    SELECT DATEADD(month, 1, '11-JAN-2012');
    # 2012-02-11 00:00:00.000 
 
    -- Subtract 1 month from January 11, 2012
    SELECT DATEADD(mm, -1, '11-JAN-2012');
    # 2011-12-11 00:00:00.000

Last Day - ADD_MONTHS and DATEADD Can Change Day

If the resulting month has fewer days than the day in the input value, then both Oracle ADD_MONTHS and SQL Server DATEADD set the day to the last day of the resulting month:

For example, adding 1 month to January 31, 2012 gives February 29, 2012:

Oracle:

    SELECT ADD_MONTHS('31-JAN-2012', 1) FROM dual;
    # 29-FEB-2012

SQL Server:

    SELECT DATEADD(month, 1, '31-JAN-2012');
    # 2012-02-29 00:00:00.000

Alternatives - Using Datetime Arithmetic

If you know the exact number of days between the specified date intervals, you can use + operator to get the resulting date both in Oracle and SQL Server.

Oracle:

    -- Add 1 month to January 31, 2012
    SELECT TO_DATE('31-JAN-2012') + 29 FROM dual;
    # 29-FEB-2012

SQL Server:

    -- Add 1 month to January 31, 2012
    SELECT CONVERT(DATETIME, '31-JAN-2012') + 29;
    # 2012-02-29 00:00:00.000

For more information, see Datetime Arithmetic in Oracle and SQL Server.

Database and SQL Migration Tools

About SQLines

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko, dmtolpeko@sqlines.com - February 2013.

You could leave a comment if you were logged in.