CODEWITHSUNDEEP
sqlsql-server
user716255
user716255

Reputation: 443

How can force SQL DateDiff function to stop rounding up?

I am working on a query that tries to count from an exact date to an exact date. Today is 2/16/2022. Between 6/30/2020 and 2/16/2022 19 months have gone by. My DateDiff query returns 20? How can I force DateDiff function to return 19?

select datediff(month, '6/30/2020', getdate())

Upvotes: 0

Views: 445

Answers (2)

user716255
user716255

Reputation: 443

Here is the solution that worked for me.

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

link to fiddle

Upvotes: 0

ub_coding
ub_coding

Reputation: 139

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)

Upvotes: 1

Related Questions