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
Getting Student Name With Highest Total Mark in SQL
Presto SQL - Converting a Date String to Date Format
How to Search Json Array in MySQL
Want to Run Multiple SQL Script File in One Go With in Sqlplus
How to Use Select Distinct and Concat in the Same SQL Statement
Database Table With Million of Rows
Trim Whitespaces (New Line and Tab Space) in a String in Oracle
How to Store Output of a SQL Server Stored Procedure in a .Txt File
Executing SQL Scripts on Docker Container
Selecting First and Last Values in a Group
Remove Decimal Values Using SQL Query
Convert Utc Milliseconds to Datetime in SQL Server
Extract Number from String With Oracle Function
Compare 2 Column Values in Same Table
Sqlstate[01000]: Warning: 1265 Data Truncated for Column
Sql to Find Upper Case Words from a Column