Calculate years,date and days,

Analyst_SQL 3,551 Reputation points
2023-04-05T07:06:11.7766667+00:00

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 

User's image

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,870 questions
0 comments No comments
{count} votes

Accepted answer
  1. CosmogHong-MSFT 23,561 Reputation points Microsoft Vendor
    2023-04-05T08:25:59.37+00:00

    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

    0 comments No comments

0 additional answers

Sort by: Most helpful