6

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?

2
  • 1
    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
  • 1
    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
14

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.

1
  • 1
    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.