CODEWITHSUNDEEP
sqloracle-databaseplsql
Tom Scott
Tom Scott

Reputation: 49

SQL, Date of birth to current age

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;

Upvotes: 1

Views: 471

Answers (3)

Nikhil
Nikhil

Reputation: 3950

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

Upvotes: 0

Gaurav Soni
Gaurav Soni

Reputation: 6338

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

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

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

Upvotes: 1

Related Questions