Calculate years,date and days,

Question

i am trying to calculate age ,but it is not giving me proper out

Declare @dateofbirth datetime  
Declare @currentdatetime datetime  
Declare @years varchar(40)  
Declare @months varchar(30)  
Declare @days varchar(30)  
set @dateofbirth='2022-12-29'--birthdate  
set @currentdatetime =getdate()--current datetime  
select @years=datediff(year,@dateofbirth,@currentdatetime)-- To find Years  
select @months=datediff(month,@dateofbirth,@currentdatetime)-(datediff(year,@dateofbirth,@currentdatetime)*12)  
-- To Find Months  
select @days=datepart(d,@currentdatetime)-datepart(d,@dateofbirth)-- To Find Days  
select @years  +' years,   ' +@months +' months,   '+@days   +' days' asYearMonthDay 

Answer 1

Hi @Analyst_SQL Try this:

Declare @dateofbirth datetime  
Declare @currentdatetime datetime  
Declare @thisYearBirthDay datetime  
Declare @years varchar(40)  
Declare @months varchar(30)  
Declare @days varchar(30)  
set @dateofbirth='2022-12-29'--birthdate  
set @currentdatetime =getdate()--current datetime  

SELECT @currentdatetime = GETDATE()
SELECT @thisYearBirthDay = DATEADD(year, DATEDIFF(year, @dateofbirth, @currentdatetime), @dateofbirth)
SELECT @years = -1 * (DATEDIFF(year, @dateofbirth, @currentdatetime) - (CASE WHEN @thisYearBirthDay > @currentdatetime THEN 1 ELSE 0 END))
SELECT @months = -1 * (MONTH(@currentdatetime - @thisYearBirthDay) - 1)
SELECT @days = -1 * (DAY(@currentdatetime - @thisYearBirthDay) - 1)
SELECT @years  +' years,   ' +@months +' months,   '+@days   +' days' asYearMonthDay 

Best regards,

Cosmog Hong