How to Calculate Age of a Person using SQL Codes
You can easily calculate the age of a person by using only a line of sql codes.
As MS SQL Server developers or programmers may need how many years passed between two dates just the same case while calculating the age of a person using sql query or sql queries.
And this may be a problem in some situations if you have missed some critical point that may cause you fall in error or did not make enough tests to figure out tricky situations.
Here is the sql codes for getting the age of a person :
DECLARE @BirthDate DATETIME
DECLARE @CurrentDate DATETIME
SELECT @CurrentDate = ‘20070210’, @BirthDate = ‘19790519’
SELECT
DATEDIFF(YY, @BirthDate, @CurrentDate) —
CASE
WHEN(
(MONTH(@BirthDate)*100 + DAY(@BirthDate)) >
(MONTH(@CurrentDate)*100 + DAY(@CurrentDate))
) THEN 1
ELSE 0
END
For this age calculation two parameters are required, the @BirthDate parameter is for the persons date of birth and the @CurrentDate parameter is for the date at which the t-sql codes will calculate the age of person.
In order to code in an optimized MS SQL Server development process, as sql developers or SQL Server Administrators we can move sql query codes in SQL Server as user-defined functions.
Just, create a user-defined function to get use of this t-sql code in our other sql batch codes or stored procedures to get a more readable sql code.
CREATE FUNCTION dbo.fn_CalculateAge
(
@BirthDate datetime,
@CurrentDate datetime
)
RETURNS int
IF @BirthDate > @CurrentDate
RETURN 0
DECLARE @Age int
SELECT @Age = DATEDIFF(YY, @BirthDate, @CurrentDate) —
CASE WHEN(
(MONTH(@BirthDate)*100 + DAY(@BirthDate)) >
(MONTH(@CurrentDate)*100 + DAY(@CurrentDate))
) THEN 1 ELSE 0 END
Note that this user defined sql function takes care of negative values that may occur because of fault date parameters entry. If you enter a date earlier than the birthdate of the person for age calculation, the dbo.fn_CalculateAge t-sql user define function will return 0.
You can use the below syntax while calling the CalculateAge function in your t-sql codes.
But if you actually deal with how many years have passed between two dates,
If you do not want to check the date order while passing the date parameters to the function or you really deal with how many years have passed between two dates instead of an aged calculation, you can use the below script for creating the fn_CalculateAge sql function.
ALTER FUNCTION dbo.fn_CalculateAge
(
@BirthDate datetime,
@CurrentDate datetime
)
RETURNS int
IF @BirthDate > @CurrentDate
BEGIN
DECLARE @TempDate datetime
SELECT
@TempDate = @BirthDate,
@BirthDate = @CurrentDate,
@CurrentDate = @TempDate
END
DECLARE @Age int
SELECT @Age = DATEDIFF(YY, @BirthDate, @CurrentDate) —
CASE WHEN(
(MONTH(@BirthDate)*100 + DAY(@BirthDate)) >
(MONTH(@CurrentDate)*100 + DAY(@CurrentDate))
) THEN 1 ELSE 0 END
RETURN @Age
How to Calculate Age in SQL Server
A common requirement in SQL Server databases is to calculate the age of something in years. There are several techniques for doing this depending on how accurate you want the final result to be. This blog considers three techniques, saving the most complex, but most accurate, for last.
Using DATEDIFF to Calculate Age
Apparently, the quickest and easiest way to calculate the age of someone or something in years is to simply use the DATEDIFF function.
A seemingly quick and obvious way to calculate age in years.
At first glance the DATEDIFF function seems to successfully calculate ages quickly and easily, but closer inspection reveals that it's not quite as accurate as we'd like (and certainly not as accurate as Keanu Reeves would like!). In fact, in the above list, only the last record is calculated correctly — the other three are being reported as one year older than they actually are.
The problem is that when the DATEDIFF function is told to look at years it ignores every other part of the dates involved, essentially performing the calculation shown below:
This is what DATEDIFF is really doing when you ask it to give you the difference between two dates in years.
Clearly, using DATEDIFF to calculate the difference between dates in years isn't accurate enough for something as potentially sensitive as age. In that case we need a different approach.
Calculating Age in Days and Dividing the Result
A more accurate, but not perfect, way to calculate age in years is to first work out the difference in days between two dates and then divide the result by the number of days in a year. The method is shown in the example below:
Dividing the age in days by the number of days in a year gives a slightly more accurate result. The .25 is to take into account leap years.
The last step in this type of calculation is to remove the decimal places to give the age in whole years. To do this we can convert the answer to the INT data type.
Converting the result of the above calculation to the INT data type gives us the age in years.
Clearly this method is more accurate than using DATEDIFF with years, but we're still not one hundred percent there.
Here the date range doesn’t include a leap year so we’re inaccurately reporting the age as a year younger than it should be.
The problem with this method is that smaller date ranges will give us a less accurate answer, as in the example shown above. Fortunately, there's one further method that we can use to calculate age in years correctly.
Using DATEDIFF and Correcting the Result
This is the most accurate way to calculate an age in years, but it's also the most complex expression to write. The starting point is to use the first calculation we demonstrated at the top of the page to calculate the difference in years between two dates:
The original DATEDIFF calculation reports some dates as a year older than they actually are.
The next step is to incorporate the DATEADD function into the expression to add the calculated number of years to the original date:
This calculation tells us the date on which the event reaches the age shown in the third column.
The result of the above calculation is the date on which the person or thing reaches the age that the DATEDIFF function calculates. The final step is to work out whether that date is after today's date, and if so subtract 1 from the age that DATEDIFF calculates. We can use the CASE statement to do this as follows:
Finally, an accurately calculated age!
The expression is quite difficult to read, but it is the most accurate way to calculate an age in years in SQL Server. Phew!
If you've learnt something from this blog, think how much more we could teach you about SQL! If you can't make it to one our classroom courses in the UK, you can sit on our live two-day Introduction to SQL or two-day advanced SQL course anywhere in the world.
How to calculate age (in years) based on Date of Birth and getDate()
How can I convert that to a date, and then calculate their age in years?
My data looks as follows
I would like to see:
42 Answers 42
There are issues with leap year/days and the following method, see the update below:
UPDATE here are some more accurate methods:
BEST METHOD FOR YEARS IN INT
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
Gotta throw this one out there. If you convert the date using the 112 style (yyyymmdd) to a number you can use a calculation like this.
(yyyyMMdd — yyyyMMdd) / 10000 = difference in full years
I have used this query in our production code for nearly 10 years:
You need to consider the way the datediff command rounds.
Which I adapted from here.
Note that it will consider 28th February as the birthday of a leapling for non-leap years e.g. a person born on 29 Feb 2020 will be considered 1 year old on 28 Feb 2021 instead of 01 Mar 2021.
So many of the above solutions are wrong DateDiff(yy,@Dob, @PassedDate) will not consider the month and day of both dates. Also taking the dart parts and comparing only works if they’re properly ordered.
THE FOLLOWING CODE WORKS AND IS VERY SIMPLE:
EDIT: THIS ANSWER IS INCORRECT. I leave it in here as a warning to anyone tempted to use dayofyear , with a further edit at the end.
If, like me, you do not want to divide by fractional days or risk rounding/leap year errors, I applaud @Bacon Bits comment in a post above https://stackoverflow.com/a/1572257/489865 where he says:
If we’re talking about human ages, you should calculate it the way humans calculate age. It has nothing to do with how fast the earth moves and everything to do with the calendar. Every time the same month and day elapses as the date of birth, you increment age by 1. This means the following is the most accurate because it mirrors what humans mean when they say «age».
There are several suggestions here involving comparing the month & day (and some get it wrong, failing to allow for the OR as correctly here!). But nobody has offered dayofyear , which seems so simple and much shorter. I offer:
[Note: Nowhere in SQL BOL/MSDN is what DATEPART(dayofyear, . ) returns actually documented! I understand it to be a number in the range 1—366; most importantly, it does not change by locale as per DATEPART(weekday, . ) & SET DATEFIRST .]
Определение возраста по дате рождения в MySQL
Определить возраст любого из пользователей в базе данных MySQL ее же средствами можно достаточно просто , если вычислить разницу между текущим годом и годом его рождения, а из результата вычесть единицу, если текущий день находится к началу календаря ближе, нежели день рождения юзера.
Приведенный ниже запрос выводит имя, дату рождения каждого пользователя и его возраст.
В этом запросе функция YEAR() выделяет из даты год, а RIGHT() — пять крайних справа символов, представляющих календарный день ( MM-DD ). Часть выражения, сравнивающая даты, выдает 1 или 0, что позволяет уменьшить результат на единицу, если текущий день ( CURRENT_DATE ) находится к началу календаря ближе, нежели день рождения пользователя.
Все выражение смотрится несколько неуклюже, поэтому вместо него в заголовке соответствующего столбца результатов выводится псевдоним ( age — «возраст»).
Изначально таблица users в MySQL имеет такой вид:
| id | name | birth |
|---|---|---|
| 1 | Кобзарев Михаил Николаевич | 1986-01-04 |
| 2 | Иванов Иван Иванович | 1955-12-12 |
| … | ||
| n | Пупкин Василий Петрович | 1856-03-29 |
Ссылки
Русский разработчик со стажем. Работаю с PHP, ООП, JavaScript, Git, WordPress, Joomla, Drupal.
Оптимизирую сайты под Google Page Speed, настраиваю импорты для больших магазинов на WooCommerce + WP All Import. Пишу плагины на заказ. Все мои услуги.