Calculate age between current date and birthdate

Hakan Axheim

7y1.2k

Hi,

I want to calcute the diffence between current date and the date a person is born in year.

Birthday is stored in a column with vachar(12).

I try this SQL statement:

SELECT CONVERT(DATETIME,LEFT(birthdate,8),120), DATEDIFF(YEAR,CONVERT(DATETIME,LEFT(birthdate,8),120),getdate()) as 'Age' from Person

The funny thing with this SQL is if a person is born 201809234247 (social security number) and the current date is 2019-02-22 the age result will be 1 year. This is not true - it should be 0.

Something is wrong with my SQL statement but what?

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ishan Shah7y ago
Hello, this is a simple way to calculate age.
select DATEDIFF(YEAR,CONVERT(datetime,column_name,120),GETDATE()) from table_name
Piyush Pansuriya7y ago
Hello, This is the simple way
SELECT (CONVERT(int,CONVERT(char(8),getdate(),112))-CONVERT(char(8),birthdate,112))/10000 AS AgeIntYears
Suraj Kumar7y ago
You can try the following query.
Create table Person
(
birthdate Varchar(20)
)

Insert into Person Values ('201809234247')

SELECT FLOOR(DATEDIFF(DAY, left(birthdate, 8) , getdate()) / 365.25) as Age
from person
You can learn in details from the below tread
https://stackoverflow.com/questions/1572110/how-to-calculate-age-in-years-based-on-date-of-birth-and-getdate

You can check the live online demo at below given link

https://rextester.com/BTRA66783
Madan Shekar7y ago
select DATEDIFF(customer.dob, '2010-01-01') / 365.25 as age
SELECT DATE_FORMAT(FROM_DAYS(DATEDIFF(customer.dob,'2010-01-01')), ‘%Y’)+0 AS age