How to Calculate Age (In Years) Based on Date of Birth and Getdate()

How to calculate age (in years) based on Date of Birth and getDate()

There are issues with leap year/days and the following method, see the update below:

try this:

DECLARE @dob  datetime
SET @dob='1992-01-09 00:00:00'

SELECT DATEDIFF(hour,@dob,GETDATE())/8766.0 AS AgeYearsDecimal
,CONVERT(int,ROUND(DATEDIFF(hour,@dob,GETDATE())/8766.0,0)) AS AgeYearsIntRound
,DATEDIFF(hour,@dob,GETDATE())/8766 AS AgeYearsIntTrunc

OUTPUT:

AgeYearsDecimal                         AgeYearsIntRound AgeYearsIntTrunc
--------------------------------------- ---------------- ----------------
17.767054 18 17

(1 row(s) affected)

UPDATE here are some more accurate methods:

BEST METHOD FOR YEARS IN INT

DECLARE @Now  datetime, @Dob datetime
SELECT @Now='1990-05-05', @Dob='1980-05-05' --results in 10
--SELECT @Now='1990-05-04', @Dob='1980-05-05' --results in 9
--SELECT @Now='1989-05-06', @Dob='1980-05-05' --results in 9
--SELECT @Now='1990-05-06', @Dob='1980-05-05' --results in 10
--SELECT @Now='1990-12-06', @Dob='1980-05-05' --results in 10
--SELECT @Now='1991-05-04', @Dob='1980-05-05' --results in 10

SELECT
(CONVERT(int,CONVERT(char(8),@Now,112))-CONVERT(char(8),@Dob,112))/10000 AS AgeIntYears

you can change the above 10000 to 10000.0 and get decimals, but it will not be as accurate as the method below.

BEST METHOD FOR YEARS IN DECIMAL

DECLARE @Now  datetime, @Dob datetime
SELECT @Now='1990-05-05', @Dob='1980-05-05' --results in 10.000000000000
--SELECT @Now='1990-05-04', @Dob='1980-05-05' --results in 9.997260273973
--SELECT @Now='1989-05-06', @Dob='1980-05-05' --results in 9.002739726027
--SELECT @Now='1990-05-06', @Dob='1980-05-05' --results in 10.002739726027
--SELECT @Now='1990-12-06', @Dob='1980-05-05' --results in 10.589041095890
--SELECT @Now='1991-05-04', @Dob='1980-05-05' --results in 10.997260273973

SELECT 1.0* DateDiff(yy,@Dob,@Now)
+CASE
WHEN @Now >= DATEFROMPARTS(DATEPART(yyyy,@Now),DATEPART(m,@Dob),DATEPART(d,@Dob)) THEN --birthday has happened for the @now year, so add some portion onto the year difference
( 1.0 --force automatic conversions from int to decimal
* DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),DATEPART(m,@Dob),DATEPART(d,@Dob)),@Now) --number of days difference between the @Now year birthday and the @Now day
/ DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),1,1),DATEFROMPARTS(DATEPART(yyyy,@Now)+1,1,1)) --number of days in the @Now year
)
ELSE --birthday has not been reached for the last year, so remove some portion of the year difference
-1 --remove this fractional difference onto the age
* ( -1.0 --force automatic conversions from int to decimal
* DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),DATEPART(m,@Dob),DATEPART(d,@Dob)),@Now) --number of days difference between the @Now year birthday and the @Now day
/ DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),1,1),DATEFROMPARTS(DATEPART(yyyy,@Now)+1,1,1)) --number of days in the @Now year
)
END AS AgeYearsDecimal

How to calculate age (in years) based on Date of Birth in SQL

You can use datediff to calculate their age, and then date add to find their 70th birthday. To find the first of the month afterwards, you can use the Month and Year functions.

create table #people (name varchar(30), birthdate date)

insert into #people
values ('Bob', '07/08/1976'), ('Tasha','05/30/1996'),('April','04/01/1971')

--This will give you everyone's age
select DATEDIFF(YY,birthdate,GETDATE()) as age
from #people

--This will give you the first month following the date that they turn 70
select Name, DATEADD(yy,70,birthdate) as [70thBday], convert(varchar,month(dateadd(m,1,DATEADD(yy,70,birthdate)))) + '/01/' + convert(varchar,YEAR(dateadd(m,1,DATEADD(yy,70,birthdate))))
from #people

Calculating age derived from current date and DOB

Use AS:

CREATE TABLE Normal_Users(
first_name varchar(20),
last_name varchar(20),
date_of_birth date,
age int AS (year(CURRENT_TIMESTAMP) - year(date_of_birth))
);

Generated columns in MySQL

< type> [ GENERATED ALWAYS ] AS ( < expression> ) [
VIRTUAL|STORED ] [ UNIQUE [KEY] ] [ [PRIMARY] KEY ] [ NOT NULL ]

[ COMMENT ]

If you are using SQL Server there is no need for datatype in computed columns:

CREATE TABLE Normal_Users(
first_name varchar(20),
last_name varchar(20),
date_of_birth date,
age AS (year(CURRENT_TIMESTAMP) - year(date_of_birth))
);

LiveDemo

EDIT:

For calculating age better use:

SELECT TIMESTAMPDIFF( YEAR, date_of_birth,  CURDATE()) AS age;

Your code for 2014-12-31 and 2015-01-01 will return 1 year, but really it has 0.

How to calculate age in SQL

The first problem with your query is that TIMESTAMPDIFF() is a MySQL function, not a SQL Server function. And so is CURDATE(). It is tempting to replace it with DATEDIFF(), but they do different things.

The correct logic for what you want is:

WHERE DOB < DATEADD(YEAR, -60, GETDATE())

That is, the date of birth is before today minus 60 years.

Why doesn't DATEDIFF() work? It counts the number of year boundaries between two dates. This year is 2021. So anyone born in 1961 would evaluate to 60. However, today is May 23rd, so only people born on or before May 23rd, 1961 are really 60 years old.

As a note: TIMESTAMPDIFF() works a bit differently in MySQL so it is better for calculating ages correctly. However, I still recommend the simple date comparison approach, even in that database.

How to calculate age in T-SQL with years, months, and days

Here is some T-SQL that gives you the number of years, months, and days since the day specified in @date. It takes into account the fact that DATEDIFF() computes the difference without considering what month or day it is (so the month diff between 8/31 and 9/1 is 1 month) and handles that with a case statement that decrements the result where appropriate.

DECLARE @date datetime, @tmpdate datetime, @years int, @months int, @days int
SELECT @date = '2/29/04'

SELECT @tmpdate = @date

SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE())) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE WHEN DAY(@date) > DAY(GETDATE()) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
SELECT @days = DATEDIFF(d, @tmpdate, GETDATE())

SELECT @years, @months, @days

How do I calculate someone's age based on a DateTime type birthday?

An easy to understand and simple solution.

// Save today's date.
var today = DateTime.Today;

// Calculate the age.
var age = today.Year - birthdate.Year;

// Go back to the year in which the person was born in case of a leap year
if (birthdate.Date > today.AddYears(-age)) age--;

However, this assumes you are looking for the western idea of the age and not using East Asian reckoning.

calculating age from sysdate and birthdate using SQL Server

Calculating age is not as simple as it might first appear.

If you use Datediff you get a difference in absolute years, which is not the age.

eg

select DATEDIFF(yy, '1980-12-31', getdate())

will return 32, whereas the age of the person in question is 31.

This might be accurate enough for your purposes.
More accurate, but still wrong, you can use

select convert(int,DATEDIFF(d, '1933-10-31', getdate())/365.25)

which is right most of the time.

Or you can write a more complex function....



Related Topics



Leave a reply



Submit