Results 1 to 9 of 9

Thread: Calculate age?

  1. #1
    Join Date
    Jan 2004
    Posts
    35

    Question Unanswered: Calculate age?

    I have a DOB field in my sql table, does anyone know how to display that field as an actual age in a view or a SP?

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    select CONVERT(INTEGER, getDATE() - DOB)/365 from table
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that's only approximate, and will probably be wrong for some people on some days



    here's an age calculation that's correct to the day:
    Code:
    select year(getdate())
         - year(DOB)
         - case when month(getdate())
                   > month(DOB) 
           then 0
           else 
           case when month(getdate())
                   < month(DOB) 
           then 1
           else
           case when day(getdate())
                   < day(DOB) 
           then 1 
           else 0
           end 
           end 
           end   as age
      from ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    SELECT DATEDIFF(yy, DOB, GETDATE())

    my 2 cents...
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Frettmaestro, what's your birthday?

    unless it is within the first four weeks of january, it has not yet happened this year

    so do your datediff calculation on your own birthday, and see what answer you get

    is that your correct age?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    Hmm, busted! So much for quick solutions, hehe...I would have to go with the same solution as r123456... ->

    SELECT CONVERT(int, DATEDIFF(dd, '1976-03-06 13:00:000', GETDATE()))/365

    But how is this only approximate...?
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it is only approximate because it totally messes up around the last day of february and/or first day of march, and it gets worse the older the person is
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2003
    Location
    London, England
    Posts
    106
    This means that a 80-year old person would risk to wait 20 days before his age was updated from 79 to 80. If this is the case then I guess I at least could live with that...and I bet the 80-year old guy would be thrilled, hehehe Just kidding offcourse...accuracy is *very* important in all aspects of what you do.
    Frettmaestro
    "Real programmers don't document, if it was hard to write it should be hard to understand!"

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, accuracy, what i said in my first post

    here's how it works: subtract the years, then adjust it by 1 based on whether.... oh, never mind, it was real easy to write, it should be real easy to figure out (hey, i should make that my sig, eh)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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