how to calculate age from date of birth

Question

im required to come up with a script for a solution where you need both peoples date of birth, and age, when registering them
its clearly stated that the person wont be asked to input their age, just their dob. but the age is needed
what needs to be done??

CREATE TABLE user(
ssn varchar(15) PRIMARY KEY NOT NULL,
name1 varchar(20) NOT NULL,
name2 varchar(20) NULL,
lastname1 varchar(20) NOT NULL,
lastname2 varchar(20) NOT NULL,
date_birth DATE NOT NULL,
)

Answer 1

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

Answer 2

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];

Answer 3

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.