## calculation of day, month and year of birth

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc

I would like to know how to calculate through a sql the day month and year of birth of an individual, for example: If an individual was born on 2/12/2006 and hj and 3/15/2006 he then have 3 days 1 month and 0 years of life.
How do I reach this result?
Spectreman
Location: Tóquio

DOMINANTES, ÀS ORDENS!

That calculation is always half \"problematic\" because the months may have 28, 30 or 31 days ... dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?

Man that sucks ... ... racking my brain to make a PL/SQL for q I tests right ... ... ...

Code: Select all
`DECLARE  V_DIA NUMBER;  V_MES NUMBER;  V_ANO NUMBER;  V_DT1 DATE;  V_DT2 DATE;BEGIN   V_DT1 := TO_DATE('15/03/2006','DD/MM/RRRR'); -- Data atual   V_DT2 := TO_DATE('12/02/2006','DD/MM/RRRR'); -- Data nascimento      IF V_DT1 >  V_DT2 THEN      V_DIA := TO_CHAR(V_DT1,'DD') - TO_CHAR(V_DT2,'DD');            V_MES := TO_CHAR(V_DT1,'MM') - TO_CHAR(V_DT2,'MM');            IF V_MES < 0 THEN         V_ANO := TO_CHAR(V_DT1,'RRRR') - TO_CHAR(V_DT2,'RRRR') - 1;         V_MES := TO_CHAR(V_DT2,'MM') - ABS(V_MES);      ELSE         V_ANO := TO_CHAR(V_DT1,'RRRR') - TO_CHAR(V_DT2,'RRRR');      END IF;            IF V_DIA < 0 THEN         V_DIA := TO_CHAR(V_DT2,'DD') - ABS(V_DIA);      END IF;               DBMS_OUTPUT.PUT_LINE('Hoje é dia '||TO_CHAR(V_DT1,'DD-Mon-RRRR')||' você nasceu em '||TO_CHAR(V_DT2,'DD-Mon-RRRR')||' então você tem '||TO_CHAR(V_ANO)||' ano(s) '||TO_CHAR(V_MES)||' mês(es) e '||V_DIA||' dia(s)');   ELSE      DBMS_OUTPUT.PUT_LINE('Você ainda não nasceu!!!');   END IF;END;`
Rique
Location: Santa Barbara d'Oeste - SP

Rique

There was an error in the code ... ... guess I hit. ...

Code: Select all
`DECLARE  V_DIA NUMBER;  V_MES NUMBER;  V_ANO NUMBER;  V_DT1 DATE;  V_DT2 DATE;BEGIN   V_DT1 := SYSDATE; -- Data atual   V_DT2 := TO_DATE('19/12/2007','DD/MM/RRRR'); -- Data nascimento      IF V_DT1 >  V_DT2 THEN      V_DIA := TO_CHAR(V_DT1,'DD') - TO_CHAR(V_DT2,'DD');            V_MES := TO_CHAR(V_DT1,'MM') - TO_CHAR(V_DT2,'MM');            IF V_MES < 0 THEN         V_ANO := TO_CHAR(V_DT1,'RRRR') - TO_CHAR(V_DT2,'RRRR') - 1;         V_MES := TO_CHAR(V_DT2,'MM') - ABS(V_MES);      ELSE         V_ANO := TO_CHAR(V_DT1,'RRRR') - TO_CHAR(V_DT2,'RRRR');      END IF;            IF V_DIA < 0 THEN         V_DIA := (TO_CHAR(V_DT2,'DD') - ABS(V_DIA)) + (TO_CHAR(LAST_DAY(V_DT2),'DD') - TO_CHAR(V_DT2,'DD'));      END IF;               IF TO_CHAR(V_DT1,'DD') < TO_CHAR(V_DT2,'DD') THEN         V_MES := V_MES - 1;      END IF;               DBMS_OUTPUT.PUT_LINE('Hoje é dia '||TO_CHAR(V_DT1,'DD-Mon-RRRR')||' você nasceu em '||TO_CHAR(V_DT2,'DD-Mon-RRRR')||' então você tem '||TO_CHAR(V_ANO)||' ano(s) '||TO_CHAR(V_MES)||' mês(es) e '||V_DIA||' dia(s)');   ELSE      DBMS_OUTPUT.PUT_LINE('Você ainda não nasceu!!!');   END IF;END;`

Thanks for your help Ju ... you are a well of kindness!!!
Rique
Location: Santa Barbara d'Oeste - SP

Rique

I have something more simple and I think answer:
Code: Select all
`Select (CASE      WHEN Floor(MOD(Months_Between(sysdate, '22/06/1982'), 12)) > 0 THEN          Floor(Months_Between(sysdate, '22/06/1982')/12) || ' anos e '  ||           Floor(MOD(Months_Between(sysdate, '22/06/1982'),12)) || ' meses e '||          Floor(MOD(Months_Between(sysdate, '22/06/1982'),30)) || ' dias'     ELSE          Floor(Months_Between(sysdate, '22/06/1982')/12) || ' anos'      END) IdadeFROM dual`
André Renato
Location: Sorocaba-SP

I made a small change to the code, I think now is a little more accurate. I ran the query and the year and month came right, but the day came wrong. came 2 (I was born 6 days and today is day 4) he was picking up the difference of the day of the anniversary this month (which gives two days in my case) but had to get the previous month.

Code: Select all
`Select(CASE     WHEN Floor(MOD(Months_Between(sysdate, '06/06/1978'), 12)) > 0 THEN          Floor(Months_Between(sysdate, '06/06/1978')/12) || ' anos e '  ||          Floor(MOD(Months_Between(sysdate, '06/06/1978'),12)) || ' meses e '||      abs(Floor(MOD(Months_Between(sysdate, '06/06/1978'),30)) - 30) || ' dias'     ELSE          Floor(Months_Between(sysdate, '06/06/1978')/12) || ' anos'     END)IdadeFROM dual`
NaPraia
Location: Floripa - SC