Issue with DATEADD Function

Question

I have huge calculations happening in SQL based on the dates and years. When I add months to a date its not adding based on the days, its primarily adding 3 months (thats the functionality of that functions).

eg SELECT DATEADD(month, 4, '2016-10-25'). This is fetching what I am expecting which is 2017-02-25

but when I do this DATEADD(month, 4, '2016-10-30'). Its fetching 2017-02-28. Which is not what I am expecting. I know this function merely adds months and bring it to the last day of that month.

In this case if I would like to see output as 2016-02-30 would it be possible because I know that date does not exist. or would it be possible for us to program it to return 2017-03-01 instead of 2017-02-28. (This becomes a bigger problem during leap year as we do have 29th Feb)

I really appreciate your response on this. Thank you.

Answer 1

Obviously, no reasonable software system is going to produce 2017-02-30. That is too much to ask for.

If you want to go to the next month instead of going to the last day of the month, you can do:

select (case when day(d) <= 28 or day(d) = day(dateadd(month, 4, d))
             then dateadd(month, 4, d)
             else dateadd(month, 5, dateadd(day, 1 - day(d), day)
        end)

This says:

1. Adding 4 months is fine so long as the day four months later is the same as the day of month now.
2. Otherwise, go to the beginning of the month 5 months in the later.

Answer 2

You could do some calculations based on the day number of the month being added to and add extra days where the destination month doesn't have that many days:

CREATE TABLE #dates ( val DATE );

INSERT  INTO #dates
        ( val )
VALUES  ( '20160131' ),
        ( '20160130' ),
        ( '20160129' );

SELECT  val ,
        DATEADD(MONTH, 1, val) StandardMonthAdd ,
        CASE WHEN DATEPART(DAY, val) != DATEPART(DAY, DATEADD(MONTH, 1, val))
             THEN DATEADD(DAY,
                          DATEPART(DAY, val) - DATEPART(DAY,
                                                        DATEADD(MONTH, 1, val)),
                          DATEADD(MONTH, 1, val))
             ELSE DATEADD(MONTH, 1, val)
        END CalculatedMonthAdd
FROM    #dates;

DROP TABLE #dates;

Produces:

val         StandardMonthAdd    CalculatedMonthAdd
2016-01-31  2016-02-29          2016-03-02
2016-01-30  2016-02-29          2016-03-01
2016-01-29  2016-02-29          2016-02-29

This assumes that for record 1 because February doesn't have 31 days you want to add 2 days and for record 2, you add 1 day.

Answer 3

Try this for 31st Feb etc

    -- ================================================
-- Template generated from Template Explorer using:
-- Create Scalar Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
create FUNCTION fn_Add_Full_Months
(
    -- Add the parameters for the function here
    @DATE as datetime,
    @MONTHADD as int
)
RETURNS nvarchar(10)
AS
BEGIN
    -- Declare the return variable here

    DECLARE @DAYS as int = DAY(@DATE);
    DECLARE @MONTHS as int = MONTH(@DATE);
    DECLARE @YEARS as int = YEAR(@DATE);

    DECLARE @TRIAL as int = @YEARS*12 +  @MONTHS - 1 + @MONTHADD;

    RETURN CAST(@TRIAL / 12 AS nvarchar(4)) + '-' + 
                RIGHT('0' + CAST(@TRIAL % 12 + 1 AS nvarchar(4)), 2)  + '-' + 
                RIGHT('0' + CAST(@DAYS  AS nvarchar(4)), 2);

END
GO

then

select dbo.fn_Add_Full_Months('19960131', 1);