April 2, 2017 at 11:55 pm
Sergiy - Friday, March 31, 2017 6:30 PMLynn Pettis - Friday, March 31, 2017 8:19 AMSergiy - Friday, March 31, 2017 5:39 AMNadrek - Thursday, March 30, 2017 5:02 PMSergiy - Thursday, March 30, 2017 4:14 PMLynn Pettis - Thursday, March 30, 2017 3:32 PMHad this discussion years ago. It sometimes comes down to a jurisdictional call. Some areas will say leaplings turn a year older on February 28th in years that are not leap years and others will say that these individuals turn a year older on March 1st.
There is apparently no universal standard for this.
Yep, there is no 100% certainty about the right approach.
But I had to pick one to use in my calculations.
So I decided to stay with the one used by DATEADD function in T-SQL.
From my experience, it's the one used in most of situations by people in different countries.That is why I suggested have a parameter to select each of them.
Even in the same country, you may need both calculations, Feb 28 in some queries and Mar 1 in others.
If we change the behavior for 29 Feb then, for sake of consistency, we need to change it for 31 Jan, 31 Mar, etc.
If 1 year from 29 Feb is 1 Mar, then 1 month from 31 Jan must be 1 Mar.I don't want to say it would be wrong.
But it would be a big change, with the outcome unexpected by most of the users.I don't agree with your logic. The reason is that there will always be January 31st, March 31st, April 30th, May 31st, June 30th, July 31st, August 31st, September 30th, October 31st, November 30th and December 31st in every year. Only February can have a different month end date due to leap years.
My point it - we should use the same logic for all "lack of days in the month" situations.
If adding a month to May 31 gives us June 30, not July 1, then adding a year to Feb 29 must give Feb 28, not Mar 1.
And that is what DATEADD(year,1,'2016-02-29') will do.
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply