CODEWITHSUNDEEP
sqlsql-serveroracle-databasedate-arithmetic
Jacob Saylor
Jacob Saylor

Reputation: 2381

SQL Server version of Oracle's ADD_MONTHS()

In Oracle, you can use ADD_Months to add months on the fly in the sql statement. What is the MS SQL version.

Oracle Example

Select TestDate, 
       TestFrequency,
        ADD_MONTHS(TestDate, TestFrequency) AS FutureTestDate 
  FROM Tests

Source : java's website

Upvotes: 1

Views: 18116

Answers (4)

Jersey_Guy
Jersey_Guy

Reputation: 907

CREATE FUNCTION [dbo].[ADD_MONTHS]
(
    @inDate SMALLDATETIME,
    @inFrequency INT

)
RETURNS DATETIME
AS
BEGIN
    RETURN DATEADD(MONTH, @inFrequency, @inDate)
END


-- TO Call : 
-- SELECT dbo.ADD_MONTHS(3,getdate()) AS newDate

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332501

SQL Server's TSQL equivalent to Oracle's PLSQL ADD_MONTHS function is DATEADD:

SELECT TestDate, 
       TestFrequency,
       DATEADD(mm, TestFrequency, TestDate)
  FROM TEST

Upvotes: 2

Alex K.
Alex K.

Reputation: 175748

Its DATEADD(MONTH, TestFrequency, TestDate) to add TestFrequency number of months to the date field TestDate.

Upvotes: 8

Charles Bretana
Charles Bretana

Reputation: 146409

I'm not exactly sure how Oracles Add_Months works, but MS Sql has this:

   Declare @NumMonthsToAdd TinyInt Set @NumMonthsToAdd  = 6
   Declare @aDate DateTime Set @aDate = '12 Jan 2010'
   Select DateAdd(month, @numMonthstoAdd, @aDate)
      -- above will generate datetime of '12 July 2010'

Upvotes: 1

Related Questions