Calculating age from incomplete SQL data

Question

Two columns in table looks like this:

Year of birth	ID
2005	-
1997	-
85
-	95...

How do I create a SQL SELECT from all the data that will return the age of each person based only on the year of birth, and if the whole is not given or only the ID is given, then:

-if only two digits of the year are given such as 85 then by default the year of birth is 1985

-if no year is given then on the basis of the ID whose first two digits are the year of birth as above i.e. ID 95...- first two digits are 95 so the year of birth is 1995

@Sergey it would be much simpler to impose a constraint on the columns but unfortunately I have to make a select from what is there — FromSpace, Jun 13 '22 at 18:40
But you might define a function which uses [CenturyStart](https://magic.secure.force.com/PublicKnowledge/articles/bl_Reference/Century-Start-xpa-3x) (Like is defined in Magic, the platform I am programming in...) — Luuk, Jun 13 '22 at 19:03

score 0 · Answer 1 · answered Jun 13 '22 at 18:52

Firstly, I would suggest structuring your database in a cleaner way. Having some years formatted as four digits (e. g. 1985), and others as two is confusing and causes issues such as the one you have run into.

That being said, here is an ad-hoc transact sql formula that will calculate the age based on the incomplete data.

IF 'Year of Birth' IS NULL
     SELECT YEAR(NOW()) - (1900 + CAST(LEFT('ID',2) AS INT));
ELSE
     IF 'Year of Birth' < 100
          SELECT YEAR(NOW()) - (1900 + 'Year of Birth');
     ELSE
          SELECT YEAR(NOW()) - 'Year of Birth'

This code is untested, and I assumed that the ID column is a string. You'll likely have to make adjustments to make it actually work for your database

To fix the structure of your table, however, a better approach might be cleaning the data and then calculating the date, using the following commands

Filling in null year values:

UPDATE table_name
SET 'Year of Birth' = CAST(LEFT('ID',2) AS INT)
WHERE IS_NULL('Year of Birth')

Making all year values 4 digits long:

UPDATE table_name
SET 'Year of Birth' = 1900 + 'Year of Birth'
WHERE 'Year of Birth' < 100

Now, you can simply subtract the current year from the 'Year of Birth' Column to calculate the age.

Good Luck!

Here is some relevant documentation

If-Else in SQL

Year Function in SQL

String Slicing in SQL

Casting Strings to Integers in SQL

I am less than a beginner, thank you for such explanations – FromSpace Jun 14 '22 at 08:28 — FromSpace, Jun 14 '22 at 08:28

bvy · Answer 2 · 2022-06-13T19:17:40.493

Lots of opportunities to clean up what you started with, and lots of open questions too, but the code below should get you started.

drop table if exists #x
create table #x (YearOfBirth nvarchar(4), ID nvarchar(50))
insert into #x values 
    ('2005', NULL), 
    ('1997', NULL), 
    ('85', NULL), 
    (NULL, '951234567890')

select 
    year(getdate()) - 
    case when len(isnull(YearOfBirth, '')) <> 4 
    then year(convert(date, '01/01/' + 
        case when YearOfBirth is NULL 
        then left(ID, 2) 
        else YearOfBirth end)) 
    else YearOfBirth end 
as PossibleAge 
from #x
where (isnumeric(YearOfBirth) <> 0 and len(YearOfBirth) in (2, 4)) 
or (YearOfBirth is NULL and isnumeric(ID) <> 0)

One and three digit years will be ignored. Lots of ways to adjust this, but without knowing data types, etc. it's just meant to be a rough start.

It would be simpler to have in advance imposed how the data in the column should look like but unfortunately. Thank you for your explanations. — FromSpace, Jun 14 '22 at 08:29

score 0 · Answer 3 · answered Jun 13 '22 at 18:57

You can follow these steps:

filter out all null values (using the WHERE clause and the COALESCE function)
transform each number to a valid year
- year of birth has length 2 > map it to a value smaller than the current year (e.g. 22 -> 2022, 23 -> 1993)
- year of birth has length 4 > skip
cast the year of birth string to a number
compute the difference between current year and retrieved year

Here's the full query:

WITH cte AS (
    SELECT COALESCE(yob, ID) AS yob
    FROM tab 
    WHERE NOT (yob IS NULL AND ID IS NULL)
)
SELECT yob,
       YEAR(NOW()) - 
       CASE WHEN LENGTH(yob) = 2
              THEN IF(CONCAT('20',yob) > YEAR(NOW()), 
                      CONCAT('19',yob),
                      CONCAT('20',yob)              )
            WHEN LENGTH(yob) = 1
              THEN CONCAT('200', yob)
            ELSE yob
            END +0 AS age
FROM cte

Check the demo here.

bloodyKnuckles · Accepted Answer · 2022-06-13T19:45:57.827

0

MySQL

A simple example of using MySQL CASE function:

SELECT
CASE
    WHEN year_of_birth REGEXP '^[0-9]{4}$' THEN year_of_birth
    WHEN year_of_birth REGEXP '^[0-9]{2}$' THEN CONCAT("19", year_of_birth)
    ELSE CONCAT("19", ID)
END as year_of_birth
FROM Accounts;

First, check for 4 digit year_of_birth, if not found, check for 2 digit, if not found then get ID. Using CONCAT function to prepend "19" to the 2 digit year and 2 digit ID. Also using REGEXP to check for 4 or 2 digit years.

Try it here: https://onecompiler.com/mysql/3y6yc7mv2

edited Jun 13 '22 at 19:45

answered Jun 13 '22 at 19:40

bloodyKnuckles

11,551
3
29
37

Thank you very much, this has brightened up a lot for me. – FromSpace Jun 14 '22 at 08:27

Calculating age from incomplete SQL data

4 Answers4