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
  

Poston Wed, 15 Mar 2006 10:23 am

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!

Poston Tue, 02 Sep 2008 3:06 pm

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?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Wed, 03 Sep 2008 12:24 pm

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

Poston Wed, 03 Sep 2008 2:48 pm

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;
Forehead there!!!!



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

Rique

Poston Thu, 04 Sep 2008 10:52 am

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
)
Idade
FROM dual
André Renato
Location: Sorocaba-SP

Poston Thu, 04 Sep 2008 5:01 pm

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
)
Idade
FROM dual
NaPraia
Location: Floripa - SC


  • See also
    Replies
    Views
    Last Post


    Return to SQL

    Who is online

    Users browsing this forum: No registered users and 2 guests