how to calculate age from date of birth

If you want to use DATEDIFF(year, date_birth, GETDATE()), you need the CASE statements to to get the real ages:

SELECT *,  
    CASE 
        WHEN MONTH(GETDATE()) > MONTH(date_birth) OR MONTH(GETDATE()) = MONTH(date_birth) AND DAY(GETDATE()) >= DAY(date_birth) THEN DATEDIFF(year, date_birth, GETDATE()) 
        ELSE DATEDIFF(year, date_birth, GETDATE()) - 1 
    END AS age
FROM [User];

This is a very common exam/interview question for SQL. You can google for quite a few different approaches to this problem. The easiest solution (ignoring leap years and whatnot) is to use DATEDIFF. The recommended solution on Stack Overflow for example is this.

SET @as_of = GETDATE()
SET @bday = 'mm/dd/yyyy'
(0 + Convert(Char(8),@as_of,112) - Convert(Char(8),@bday,112)) / 10000

Hi @ganeplay506 ,

Welcome to Microsoft Q&A!

Please also refer below example:

insert into [user] values  
('S001','Ann',NULL,'Green','Red','1999-04-23'),  
('S002','Grace','Bob','Blue','Yellow','2003-11-10')  
  
select *,DATEDIFF(MONTH,date_birth,GETDATE())/12 age from [user]  

Output:

ssn	name1	name2	lastname1	lastname2	date_birth	age  
S001	Ann	NULL	Green	Red	1999-04-23	22  
S002	Grace	Bob	Blue	Yellow	2003-11-10	17  

If it is not working, please provide more sample data and expected output.

Thank you for understanding!

Best regards,
Melissa

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.