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.
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:
This says: