Calculate age between current date and birthdate

Question

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?

Piyush Pansuriya · Answer

Hello, This is the simple way SELECT ( CONVERT ( int , CONVERT ( char (8),getdate(),112))- CONVERT ( char (8),birthdate,112))/10000 AS AgeIntYears

Madan Shekar · Answer

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

Ishan Shah · Answer

Hello, this is a simple way to calculate age. select DATEDIFF(YEAR,CONVERT(datetime,column_name,120),GETDATE()) from table_name

Suraj Kumar · Answer

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