Issue with DATEADD Function

0
votes

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
sqlsql-serverdatedateadd
but what would be the result you actually want?. You might want to add days instead, but you are gonna have to deal with some months that have 28, others 30, 31.... – Lamak
please provide context as to why you are adding months and what the goal is with the result so you can get a suitable answer – Tanner
2016-02-30 is not a valid date. It should return feb 29 in a leap year, though-- have you tried that? 2016 is a leap year, not 2017. I agree with the days approach if that's what you need. some months have 31 days back-to-back (July and August, December and January), so months doesn't seem like a consistent interval if you are expecting that functionality. – ps2goat
So, you just want to add 4 days? SELECT DATEADD(month, 4, '2016-10-25' – JDavila
Another option is DateAdd(DD,120,somedate) at least this is a standard measure – John Cappelletti

3 Answers

1
votes

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.

0
votes

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

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