Datediff get wrong months calculations.

Because there is only 17 FULL months between the 2 dates.
SELECT DATEDIFF(month, '2018/11/01', '2020/05/01') AS DateDiff;
Gives 18 months.

DATEDIFF does not calculate full months it just subtract the months between 2 dates.
In the following example there are only 2 hours between 2 dates but DATEDIFF shows even 1 month difference:

select datediff(month, '2018-11-30 23:00:00', '2018-12-01 01:00:00') [months],
       datediff(hour,  '2018-11-30 23:00:00', '2018-12-01 01:00:00') [hours]

Select allOpen in new window

So in reality between '2018/11/01' and '2020/04/30' there are only 17 months difference.

Hi All,

Which method should I use to get the nearest to manual calculation, disregards the time.?
Thank you.

You can use:

datediff(month, '2018-11-30 23:00:00', '2018-12-01 01:00:00') + 1

Select allOpen in new window

or

datediff(day, '2018-11-30 23:00:00', '2018-12-01 01:00:00') / 30

Select allOpen in new window

none of them accurate because months have not the same number of days (furthermore years have not the same number of days).

Hi All,

Thank you very much for your help.

Thank you all.

Hi,

Technically datediff counts edges. For instance, the following ALL (apart from weeks - thrown in for completeness to confuse myself) return 1, because there is 1 edge between the two datetimes.

HTH
  David

use tempdb
go


declare @StartDateTime as DateTime
declare @EndDateTime as DateTime


set @StartDateTime = '2007-12-31 23:59:59'
set @EndDateTime = '2008-01-01 00:00:00'


set nocount on
select @StartDateTime, @EndDateTime


select datediff( year, @StartDateTime, @EndDateTime ) as Years
select datediff( quarter, @StartDateTime, @EndDateTime ) as Quarters
select datediff( month, @StartDateTime, @EndDateTime ) as Months
select datediff( week, @StartDateTime, @EndDateTime ) as Weeks
select datediff( day, @StartDateTime, @EndDateTime ) as Days
select datediff( hour, @StartDateTime, @EndDateTime ) as Hours
select datediff( minute, @StartDateTime, @EndDateTime ) as Minutes
select datediff( second, @StartDateTime, @EndDateTime ) as Seconds

Select allOpen in new window