Issue with DATEADD Function

2.9k views Asked by At

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.

3

There are 3 answers

0
Gordon Linoff On

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.
1
Tanner On

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.

2
Cato On

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);