DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: How best to calculate a persons age?

  1. #1
    Join Date
    Jul 2003
    Posts
    2

    How best to calculate a persons age?

    Hi to all,

    What method would you all use to calculate someones age?

    This is what I'm doing, just seems like there's gotta be a better way.
    (and I'm not sure this is always accurate either)

    ln_age := FLOOR((adt_cycle_date - ld_dob) / 365.25);


    Thanks!

  2. #2
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    Where's the rest the story?
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  3. #3
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    Code:
    appdev@NICK817.TARRY.LOCAL> select (sysdate - to_date('23/09/1970','dd/mm/yyyy')
    )/365.25
      2  from dual
      3  /
    
    (SYSDATE-TO_DATE('23/09/1970','DD/MM/YYYY'))/365.25
    ---------------------------------------------------
                                             32.8512056
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  4. #4
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    Or if you want this...

    Code:
    
    SQL*Plus: Release 9.2.0.3.0 - Production on Wed Jul 30 21:45:18 2003
    
    Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
    
    Enter user-name: appdev@nick920
    Enter password:
    
    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
    With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.3.0 - Production
    
    appdev@NICK920.US.ORACLE.COM> create table t
      2  (name varchar2(30),
      3  dob date)
      4  /
    
    Table created.
    
    appdev@NICK920.US.ORACLE.COM> insert into t
      2  values('nick','10-06-2001')
      3  /
    values('nick','10-06-2001')
                  *
    ERROR at line 2:
    ORA-01843: not a valid month
    
    
    appdev@NICK920.US.ORACLE.COM> insert into t
      2  values('nick','10-jun-01')
      3  /
    
    1 row created.
    
    appdev@NICK920.US.ORACLE.COM> select months_between(sysdate,dob)
      2  from t
      3  where name like 'ni%'
      4  /
    
    MONTHS_BETWEEN(SYSDATE,DOB)
    ---------------------------
                      25.674471
    
    appdev@NICK920.US.ORACLE.COM> /* or if you want in days, etc...*/
    appdev@NICK920.US.ORACLE.COM>
    
    
    appdev@NICK920.US.ORACLE.COM> select trunc(months_between(sysdate,dob)/12) years,
      2                                  mod(trunc(months_between(sysdate,dob)),12) months,
      3                                  sysdate - add_months(dob,trunc(months_between(sysdate,dob))) days
      4  from t
      5  where name like 'ni%'
      6  /
    
         YEARS     MONTHS       DAYS
    ---------- ---------- ----------
             2          1 20.9149537
    
    appdev@NICK920.US.ORACLE.COM>
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    Re: How best to calculate a persons age?

    Originally posted by RBC
    How best to calculate a persons age?
    Cut them in half an count the rings. Or do you want a non destructive method?

  6. #6
    Join Date
    May 2002
    Posts
    2,645
    And look, you even get a wrong date format tossed in for free! Now, that is what makes this board such a great place.

  7. #7
    Join Date
    Jul 2003
    Posts
    2
    wow, thanks for the effort!

    Here's what I've changed it to so far.
    ln_age := FLOOR(MONTHS_BETWEEN(adt_cycle_date,ld_dob)/12);

    I can't find errors with this (so far...)

    Thanks again!

  8. #8
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    Oh.. Ok, Glad to be able to help...
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  9. #9
    Join Date
    May 2002
    Posts
    2,645
    Calculate an age when, over which years? There is the issue of missing days in September, 1752...

  10. #10
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    Re: Re: How best to calculate a persons age?

    Originally posted by DaPi
    Cut them in half an count the rings. Or do you want a non destructive method?
    ROTFL!!
    Jeff Hunter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width