# How to get exact year difference between two dates in SQL Server?

I have a date of birth column to which I need to compare the current date and return the difference as age. Currently I am using the following query :

``````DECLARE @DateOfBirth date = '2000-07-27'
DECLARE @CurrentDate date = '2015-07-25'

SELECT
@Age = DATEDIFF(YEAR, @DateOfBirth, @CurrentDate)
``````

I am getting a difference as 15, but the desired result should be 14. This is because it compares the year and gets the difference between 2015 and 2000. So I used the following query :

``````select
@Age = CAST(datediff(day, @DOB, @CurrentDate) / 365.2425 AS INT)
RESULT : 14
``````

But when I change the Date Of Birth value to '2000-07-25', I still get the value of 14, but it should be 15 since the date difference is exactly 15 years.

How can I get the exact year difference from this?

• There's quite many examples here: stackoverflow.com/questions/1572110/… -- which is the first link I get if I google "age SQL Server" – James Z Jul 25 '15 at 8:05
• But most of the answers to that question are rubbish and get thrown by leap years. The YYYYMMDD method works every time. – Rob Farley Jul 25 '15 at 8:13

## 1 Answer

Subtract one from each other in YYYYMMDD format, and divide by 10000 using integer division (which rounds down).

So:

``````(YEAR(@CurrentDate) * 10000 + MONTH(@CurrentDate) * 100 + DAY(@CurrentDate)
- YEAR(@DateOfBirth) * 10000 - MONTH(@DateOfBirth) * 100 - DAY(@DateOfBirth)
) / 10000
``````

20150725-20000727 = 149998, and 149998 / 10000 = 14

You can also use `CONVERT(int,CONVERT(char(8),@CurrentDate,112))`, because style 112 is YYYYMMDD, but that's a little obfuscated in my opinion.

• I agree the 112 style conversion method isn't as intuitive but here's the code for completeness: `(CAST(CONVERT(char(8), @CurrentDate, 112) AS int) - CAST(CONVERT(char(8), @DateOfBirth, 112) AS int)) / 10000` – Dan Guzman Jul 25 '15 at 20:41