SQL, Date of birth to current age

Question

So ive been given a table containing data with birthdates like 540401-4428 (yymmdd - last four number(personal identity number) Im trying to figure out how Im supposed to use that number to calculate to current age and how many months. At the moment PNR just prints the whole number.

fnamn=firstname
enamn=lastname
PNR = date of birth

Supposed to look something like this:

Maria, Stjärnkvist, 33,5 år.
Leyla, Errstraid, 42,2 år.
Arne, Möller, 76,6 år.

This is how far i've come:

declare 
cursor c_användare 
is select UPPER(SUBSTR(fnamn,1,1)) || SUBSTR(fnamn,2),Upper(substr(Enamn,1,1))
|| substr(enamn,2) , PNR
from bilägare; 
v_fnamn bilägare.fnamn%type; 
v_enamn bilägare.enamn%type; 
v_pnr bilägare.pnr%type; 

begin 
if not c_användare%isopen then 
open c_användare; 
end if; 
loop 
fetch c_användare 
into v_fnamn,v_enamn,v_pnr; 
exit when c_användare%notfound; 
dbms_output.put_line(v_Fnamn||', '||v_Enamn||', '||v_pnr||'år'); 
 end loop; 
 close c_användare; 
end;

Thats just an example, somehow in the select im supposed to manage a function with PNR so it counts the birthnumbers to a current age and month like above in the example. — Tom Scott, Oct 04 '18 at 11:51
what should your function do and how should it convert, please edit you question and provide more Information with real example.. this pnr should be converted to that age.. — hotfix, Oct 04 '18 at 11:55
so what format does it adhere to? is it DDMMYY-HH24MI? so e.g. 231280-1520 would mean the person is born on the 23rd of December 1980 at 15:20? — Peter, Oct 04 '18 at 11:57
want it to be converted from DDMMYY-xxxx to actual year they are today and how many months. — Tom Scott, Oct 04 '18 at 12:15
https://stackoverflow.com/questions/33343596/how-to-get-age-in-years-months-and-days-using-oracle — hotfix, Oct 04 '18 at 12:19
@RichardHansell I would assume the format is DDMMRR as this is how these birth date + 4 digit number codes usually work — Peter, Oct 04 '18 at 12:25
540401-4428 should be interpreted as 1954 Apr 01. The last digits is part of the social security number and can be disregarded. — W_O_L_F, Oct 04 '18 at 12:50
Something like "datediff(year,pnr,sysdate)" may something similar work? — Tom Scott, Oct 04 '18 at 12:58
@richard Hansell same question by me also what about people born after 2000??? how will the be represented 00 will suggest what 1900 or 2000 2100 what?? — Nikhil S, Oct 05 '18 at 08:41

Thorsten Kettner · Answer 1 · 2018-10-04T13:36:29.873

1

The first step is to interpret the two-digit year. You don't get this out-of-the-box. Oracle knows the formats YY and RR, but 48 for instance is 2048 for both of them, while you want it to be 1948.

The current age is a bit tricky, too. That's the difference of the birthdate and today in years minus one year if the day is not yet reached.

And months are always something strange to calculate with, because they have no fixed length. We must hence live with an approximate.

with proper as
(
  select
    fnamn,
    enamn,
    case when to_date(substr(pnr, 1, 6), 'yymmdd') >= trunc(sysdate) 
      then to_date(substr(pnr, 1, 6), 'yymmdd') - interval '100' year(3)
      else to_date(substr(pnr, 1, 6), 'yymmdd')
    end as birthdate
  from bilägare
)
select
  fnamn,
  enamn,
  birthdate,
  extract(year from sysdate) - extract(year from birthdate)
  - case when to_char(sysdate, 'mmdd') < to_char(birthdate, 'mmdd')
      then 1 else 0 
    end as age,
  round(months_between(sysdate, birthdate)) as months
from proper;

Rextester demo: http://rextester.com/OHY39782

UPDATE: As mentioned it's always unprecise to calculate with months. MONTH_BETWEEN, however, gives you a decimal number of the months difference. You may want to use that and simply divide by 12. I guess there may be slight miscalculations here and there. Play with TRUNC and ROUND or even CASE WHEN, till you are satisfied with the results.

trunc(months_between (sysdate, birthdate) / 12) as age_years
trunc(mod(months_between (sysdate, birthdate), 12)) as age_months

edited Oct 04 '18 at 13:36

answered Oct 04 '18 at 12:58

Thorsten Kettner

89,309
7
49
73

Thank you for ur help, learned a lot reading it. But I am having a hard time implementing it into my alredy existing code. – Tom Scott Oct 04 '18 at 13:04
That shouldn't be too hard, as the query already does all the work. In PL/SQL you can for example use `for rec in ( < above query here> ) loop dbms_output.put_line(rec.fnamn || ' ' || rec.enamn || ': ' || rec.age); end loop;` – Thorsten Kettner Oct 04 '18 at 13:07
I just saw that you don't want the total months, but the age in years and months. This is a bit complicated again. To get the months you may want to compare `extract(month from birthdate)` and `extract(month from sysdate)`. But then again, when do you count a full month? So maybe you'd rather count intervals of thirty days? You should first decide on how to calculate. Only then implement it in SQL. – Thorsten Kettner Oct 04 '18 at 13:20
I also had a wrong month comparision for the age in my query. I've just corrected it. – Thorsten Kettner Oct 04 '18 at 13:28
Appreciatete all the help Thorsten! But im hesitating. Pretty much just started with PLSQL and it feels like there should be an easier way calculating? As the tutor hasnt gone thru anything near complex that u described. Shouldnt it be able to do something like this, see link - https://imgur.com/a/giwwmb5 instead? – Tom Scott Oct 04 '18 at 17:19
The code in the image: The format string 'yyyy...' doesn't match the string you describe ('54...'). date - date gets days, i.e. a number. trunc(number, year) doesn't seem to make sense. – Thorsten Kettner Oct 04 '18 at 17:45
Easy/complex: Well, one *has* to be careful to interpret the two-digit year correctly. This is not difficult or complex, it's just something to be aware of. Then, maybe my solution is rather complex, because it is SQL only. If a query can be written in SQL, we usually don't use PL/SQL. PL/SQL, however, being a programming language (which SQL is not) knows loops and the like. This can lead to a more readable solution. – Thorsten Kettner Oct 04 '18 at 17:46
In the end it all really depends on how precise the result is to be. You must decide for an approach. As shown, you can simply use `MONTHS_BETWEEN` for the calculation. – Thorsten Kettner Oct 04 '18 at 17:46
Interesting! I found this code " select months_between (TRUNC(sysdate), to_date('15-Dec-2000','DD-MON-YYYY') )/12 as age from dual;" but I dont know how to implement it in my code for it to work. Somehow gotta change somewhere that it retrieves my variable's (PNR) data instead of a specific date set. – Tom Scott Oct 04 '18 at 17:50
PNR insted of specific dataset? You already select pnr from bilägare and so do I. Apart from this, this is about what I said down in my answer; you can calculate years and months from `months_between`. – Thorsten Kettner Oct 04 '18 at 18:00
https://imgur.com/a/pLzBZRP Ur example, where am I supposed to enter "pnr"? remove "birthdate" and put pnr there? It gave me another error. – Tom Scott Oct 04 '18 at 18:14
You missed the `WITH` clause between `IS` and `SELECT` where `birthdate` is defined. You see my query starts with `WITH proper AS` and I select `from proper` then. – Thorsten Kettner Oct 04 '18 at 18:45

score 0 · Answer 2 · edited Oct 04 '18 at 19:00

Please correct my understanding , i ll modify the query

WITH
    tab_data
    AS
        (SELECT 'Maria' name, '540401-4428' pnr FROM DUAL
         UNION ALL
         SELECT 'Gaurav' name, '600802-1234' pnr FROM DUAL
         UNION ALL
         SELECT 'Rohan' name, '881011-9898' pnr FROM DUAL)
SELECT name,
       REGEXP_SUBSTR (pnr,
                      '[^-]+',
                      1,
                      2)
           id,
       REGEXP_SUBSTR (pnr,
                      '[^-]+',
                      1,
                      1)
           dob,
       TRUNC (  MONTHS_BETWEEN (SYSDATE,
                                TO_DATE (REGEXP_SUBSTR (pnr,
                                                        '[^-]+',
                                                        1,
                                                        1),
                                         'RRMMDD'))
              / 12)
           year,
       TRUNC (MOD (MONTHS_BETWEEN (SYSDATE,
                                   TO_DATE (REGEXP_SUBSTR (pnr,
                                                           '[^-]+',
                                                           1,
                                                           1),
                                            'RRMMDD')),
                   12))
           months
  FROM tab_data;

Result:

+--------+------+--------+------+-------+
|  NAME  |  ID  |  DOB   | YEAR | MONTH |
+--------+------+--------+------+-------+
| Maria  | 4428 | 540401 |   64 |     6 |
| Guarav | 1234 | 600802 |   58 |     2 |
| Rohan  | 9898 | 881011 |   29 |    11 |
+--------+------+--------+------+-------+

Nikhil S · Answer 3 · 2018-10-09T17:35:50.773

this will work:

select floor(to_number(sysdate- 
to_date('19'||substr('540401',1,instr('540401-4428',' - 
')-1),'YYYYMMDD'))/365)||'years' as years
,floor(((to_number(sysdate-to_date('19'||substr('540401',1,instr('540401- 
4428','-')-1),'YYYYMMDD'))/365)-
floor((to_number(sysdate-to_date('19'||substr('540401',1,instr('540401- 
4428','-')-1),'YYYYMMDD'))/365)))*10)*1.2||'months' as months
from dual;

output:

64years 6months

SQL, Date of birth to current age

3 Answers3