SQL get the exact date difference between two dates

Question

I need to calculate the exact difference between the two dates

For example difference between date1= '2011-06-27' and date2= '2013-06-27' should return 24 months whereas for date1= '2011-06-27' and date2= '2013-06-29' should return 24 months 2 days.

I tried the below logic which is converting the difference into days and dividing by 30 but that gives the wrong out as dividing by 30 across all months is incorrect. Is there a way to achieve this in SQL

-- Sample table T1 with Date1 and Date2 columns
CREATE TABLE T1 (
    Date1 DATE,
    Date2 DATE
);

-- Insert your actual data
INSERT INTO T1 (Date1, Date2) VALUES ('2011-06-27', '2013-06-27');

-- Calculate the difference in days and convert to months with a fraction
SELECT 
    Date1,
    Date2,
    DATEDIFF(DAY, Date1, Date2) AS DaysDifference,
    CAST(DATEDIFF(DAY, Date1, Date2) / 30.0 AS DECIMAL(10, 2)) AS MonthsWithFraction
FROM T1;

Answer 1

You'll want to count the number of times a month boundary is crossed, then add the remaining days.

This is written for SQL Server, but it should be similar.

-- Sample table T1 with Date1 and Date2 columns
CREATE TABLE T1 (
    Date1 DATE,
    Date2 DATE
);

-- Insert your actual data
INSERT INTO T1 (Date1, Date2)
VALUES ('2011-06-27', '2013-06-27')
  , ('2011-06-27', '2013-06-29')
  , ('2011-06-27', '2013-06-15');

with 
a as (
  select 
    date1
  , date2
  , datediff(month, date1, date2) as DiffMo
  from T1
),
b as (
  select
    date1
  , date2
  , DiffMo
  , datediff(day, dateadd(month, DiffMo    , date1), date2) as AdditionalDays
  , datediff(day, dateadd(month, DiffMo - 1, date1), date2) as AdditionalDays2
  from a
)

select 
  date1
, date2
, case
    when AdditionalDays < 0
      then cast(DiffMo - 1 as varchar(5)) + ' months ' + cast(AdditionalDays2 as varchar(5)) + ' days'
    else   cast(DiffMo     as varchar(5)) + ' months ' + cast(AdditionalDays  as varchar(5)) + ' days'
  end as DateDiff
from b

https://dbfiddle.uk/Ajcifhty

Answer 2

To do this manually you'll need to "borrow" days against the prior month much like how borrowing with subtraction by hand works. This logic does assume that Date1 is not later than Date2:

datediff(month, Date1, Date2) +
    case when day(Date2) < day(Date1) then -1 else 0 end as months,
case when day(Date2) < day(Date1)
    then day(dateadd(day, -day(Date1), Date2)) -- slide backward
    else day(Date2) - day(Date1) end as days

You can think of the slide this way. If you adjust both dates backward such that the starting point now begins just before the first of the month, then the final day in the span will fall on the day of month that is exactly how many extra days there are. You could also think of it as moving the end date backward across the final day of the prior month, which is how it then effectively incorporates that number of days into the logic.

Clearly one advantage of these tight expressions is they won't require CTE support or variables to hold intermediate results. Supposing you have an eomonth function available that latter half could take advantage with a more condensed expression:

case when day(Date2) < day(Date1)
    then day(eomonth(Date2, -1)) + (day(Date2) - day(Date1)) -- won't work on SAP IQ
    else day(Date2) - day(Date1) end as days2

Be careful with leap dates as always and consider questions like how even normal dates like March 31 to April 30 should be treated.