CODEWITHSUNDEEP
sql
ninesalt
ninesalt

Reputation: 4354

Calculating age derived from current date and DOB

I'm trying to calculate the age of a user based on the current year and his year of birth, but something in my syntax appears to be incorrect and I can't spot it.

CREATE TABLE Normal_Users(

first_name varchar(20),
last_name varchar(20),
date_of_birth date,
age int = year(CURRENT_TIMESTAMP) - year(date_of_birth) 
)

Why is this incorrect?

Upvotes: 4

Views: 42890

Answers (5)

RBerman
RBerman

Reputation: 391

You have to account not only for the year, but the month/day within the year. For SQL Server, below shows both cases, where year alone is used (and gets it wrong), and with the date within the year used to correct it when the birthdate month/day is later in the year than the reference (current) month day.

declare @DoB date = '6/1/2015';
declare @CurrDate date = '5/1/2021';

select YEAR(@CurrDate)-YEAR(@Dob);  -- will be incorrectly "6"

select YEAR(@CurrDate)-YEAR(@Dob) 
    - case when (MONTH(@DOB)*100 + DAY(@DOB)) > (MONTH(@CurrDate) + DAY(@CurrDate)) then 1 else 0 end;  -- correctly shows "5"

Upvotes: 1

pascuol
pascuol

Reputation: 31

How do you do it in your brain? You subtract the year of birth from current year,
Then you if the birthday already happen this year.
If Yes fine, else it means he will have this year the age (so -1 yearold)

diff of the years is easy.
how to compare month and day to know if we are before or after month*100+day which gives a number MMDD which is easy to compare bigger equal or smaller, so after birthday, day of birthday, before birthday.

Here is the principle for Sybase :

declare @test char(10)
set @test='15/02/2010'

select "age" =
case when (month(convert(datetime, @test, 103))*100)+day(convert(datetime, @test, 103)) - ((month(getdate())*100)+day(getdate())) <= 0 then
    DATEDIFF(YEAR,convert(datetime, @test, 103),getdate())
else
    DATEDIFF(YEAR,convert(datetime, @test, 103),getdate())-1
end

Upvotes: 3

benni_mac_b
benni_mac_b

Reputation: 8877

Use the as keyword instead of equals:

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)) 
)

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20804

Considering the case where the date of birth is in December and you are running the query in January, you need this sort of thing.

select int(
datediff(day, current_date, birth_date) / 365.25
) as yearsOld

The exact syntax depends on the database engine which hasn't been specified.

Upvotes: -1

Lukasz Szozda
Lukasz Szozda

Reputation: 175616

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.

Upvotes: 4

Related Questions