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, 2015 at 8:05
  • 2
    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, 2015 at 8:13

2 Answers 2

17

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, 2015 at 20:41
0

I have taken Robs method, and turned it into a function, and written a test script for it. I can no longer find any mistakes.

CREATE or alter FUNCTION dbo.CalcAge
(
@DOB date,  /* birthday */
@Today date /* either today or the day you want to calculate the age for */
)
RETURNS TABLE
AS
RETURN
    (
    -- from https://dba.stackexchange.com/questions/108078/how-to-get-exact-year-difference-between-two-dates-in-sql-server/108079#108079
    select (YEAR(@Today) * 10000 + MONTH(@Today) * 100 + DAY(@Today)
    - YEAR(@DOB) * 10000 - MONTH(@DOB) * 100 - DAY(@DOB)
    ) / 10000 as Age
)
go


drop table if exists #h
create table #h (dob date, now date, years int, months int, days int)

--     dob        now      years  months  days 
insert into #h
values 
  ('2012-02-29', '2013-02-28',  0, 11, 30)
, ('2012-02-29', '2013-03-01',  1,  0,  0)
, ('2012-02-29', '2016-02-28',  3, 11, 30) 
, ('2012-02-29', '2016-02-29',  4,  0,  0) 
, ('2012-02-29', '2016-03-31',  4,  1,  2)
, ('2012-01-30', '2016-02-29',  4,  0, 30)
, ('2012-01-30', '2016-03-01',  4,  1,  2)
, ('2011-12-30', '2016-02-29',  4,  1, 30)
, ('1822-11-04', '2022-11-04',200,  0,  0)
, ('2000-02-29', '2004-02-28',  3, 11, 30)
, ('1900-02-28', '2000-02-29',100,  0,  0)
, ('1900-02-28', '2001-02-28',101,  0,  0)
, ('2000-02-28', '2104-02-28',104,  0,  0)
, ('2000-02-29', '2104-02-29',104,  0,  0)
, ('2012-03-28', '2017-03-28',  5,  0,  0)
, ('2012-03-28', '2017-03-29',  5,  0,  0)

select *
from #h h
cross apply dbo.CalcAge (dob, now) as A
--where h.years <> a.Age
order by h.dob, h.now
go
drop function if exists dbo.CalcAge
1
  • Yes, I've been looking left,right and center to find a solution that is fast and work correctly. The list of dates to test is build on what I've found on the way. This solution worked the first time. Dec 3, 2022 at 8:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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