Today is 2/16/2022. There are 19 complemete month between 6/30/2020 and 2/16/2022.
Why is my DateDiff function returning 20?
select datediff(month, '6/30/2020', getdate())
How can I force DateDiff function to return 19?
CodePudding user response：
As per documentationSQL Docs, datediff returns The int difference between the startdate and enddate, expressed in the boundary set by datepart. which in your example is the number of months from June '20 to February '22 which is indeed 20. User user716255's code is pointing into the right direction in that it uses the first of each month in question. If your intention however is to really know how many months elapsed between two dates, the code should be amended like so: (sorry, need to correct my original answer, as I misread the code from the other answer)
declare @start date
declare @end date
set @start = '20200630'
set @end = '20220216'
select datediff(month, dateadd(day, -day(@start) 1, @start), dateadd(day, -
day(@end) 1,@end)) CASE WHEN DAY(@end)>DAY(@start) THEN 1 ELSE 0 END
(with dates written in a format more useful for our international readers...) The original coude would fail if start date would i.e. be 31st of July and the end date in February (as -31 1 would lead to a date in January)
CodePudding user response：
declare @start date
declare @end date
set @start = '6/30/2020'
set @end = '2/16/2022'
select datediff(month, dateadd(day, -day(@start) 1, @start), dateadd(day, -day(@start) 1,@end))