Solution 4 will not work if the period includes a leap year (because they have assumed 365 days in a year, or an average month length in days of 30.
E.g. try that code with a @ToDate of '2024-08-19' and it will return 3 years 1 Months and
28 Days
Try this approach instead
- Calculate the number of months between the two dates and divide by 12 to get the difference as full years
- Add that number of years to the start date to give an interim date to make life easier
- Calculate the number of DAYS between the new interim date and the To Date. Divide that number of days by
30.5 to get the number of full months between the dates.
- Add that number of months to the interim date
- Calculate the number of days between the (new) interim date and the To Date
declare @years int = (SELECT CAST(DATEDIFF(MONTH, @fromdate, @todate) / 12 as int));
declare @interimDate date = (SELECT DATEADD(YEAR, @years, @fromdate));
declare @months int = (SELECT CAST(DATEDIFF(DAY, @interimDate, @todate) / 30.5 as int));
set @interimDate = (SELECT DATEADD(MONTH, @months, @interimDate));
declare @days int = (SELECT DATEDIFF(DAY, @interimDate, @todate));
For this test data
DECLARE @FromDate DATE = '2021-06-23';
DECLARE @ToDate DATE = '2023-08-19';
you will get 2 Years 1 Months 27 Days and for this test data
DECLARE @FromDate DATE = '2021-06-23';
DECLARE @ToDate DATE = '2024-08-19';
you will get 3 Years 1 Months
27 Days