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 .
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
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
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
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.
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.