How to get the exact year difference in SQL

I need a simple way to find the exact year difference between two dates. For example between 01.11.2013 and 30.10.2019. In this case, it should be 5 years because current date has not reached 01.11 yet. But if it is between 29.10.2013 and 30.10.2019 then it should be 6 years.

Let assume these are birthday and current days. It's similar.

I've tried to get the result with the following code:

select datediff(year,20131101,20191030)

It is giving me 6 instead of 5. The closest solution I've found is:

select datediff(day,20131101,20191030)/365

But as we know there could be 1 or 2 leap years in this period and the result is again not exactly what I expect. Neither when we divide with 365 nor 366.

It is not necessary using datediff. I wonder if there is some simple way to present it or some stored function to do this for which existing I did't know.

Solution

You can take the year difference and then subtract 1 if necessary:

select (datediff(year, val1, val2) -
        (case when month(val1) < month(val2) then 0
              when month(val1) > month(val2) then 1
              when day(val1) <= day(val2) then 0
              else 1
         end)
        ) as diff_years