-1

I have an age calculation formula that is returning a negative month value. This does not happen to all values, can someone shed any light as to what the cause is and possibly provide a fix please?

Declare @dateofbirth datetime  
Declare @currentdatetime datetime  
Declare @years varchar(40)  
Declare @months varchar(30)  
set @dateofbirth='1982-12-16'--birthdate  
select concat(datediff(year,@dateofbirth,getdate()),'\',   datediff(month,@dateofbirth,getdate())-(datediff(year,@dateofbirth,getdate())*12))  [Year\Month]

RESULT : Year\Month 37-3

Thanks,

user3306489
  • 159
  • 1
  • 9

2 Answers2

1

You are implicitly assuming their birthday has already occurred this year.

Declare @dateofbirth datetime Declare @currentdatetime datetime Declare @years varchar(40) Declare @months varchar(30) set @dateofbirth='1982-12-16'--birthdate select concat(DATEDIFF(MONTH,CASE WHEN DAY(@dateofbirth) > DAY(GETDATE())THEN DATEADD(MONTH,1,@dateofbirth)ELSE @dateofbirth END,GETDATE()) / 12 ,'\' ,DATEDIFF(MONTH,CASE WHEN DAY(@dateofbirth) > DAY(GETDATE())THEN DATEADD(MONTH,1,@dateofbirth)ELSE @dateofbirth END,GETDATE()) % 12 )[Year\Month]

nosnevel
  • 7
  • 8
1

The cause is this part:

datediff(year,@dateofbirth,getdate())*12

datediff doesn't count whole years it counts boundaries crossed so the following returns 1 even though a single day has passed:

SELECT datediff(year,'2019-12-31','2020-01-01')

There are a lot of suggestions on how to accurately handle age calculations in this question here if you need to show age in years and months then it shouldn't be too hard to adapt these to what you need.

BarneyL
  • 1,332
  • 8
  • 15