Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Posts
    225

    Unanswered: age as of 31/8/2007

    how do i calculate someones age on 31/8/2007?

    i have a field with DOB in fo in it..

    i have been playing with datediff but cant get there

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select name
         , year(#2007-08-31#) 
          -year(DOB)
          -iif(month(#2007-08-31#) > month(DOB),0
          ,iif(month(#2007-08-31#) < month(DOB),1
          ,iif(  day(#2007-08-31#) < day(DOB),1,0 ))) as age
      from yourtable
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,472
    Provided Answers: 10
    hes some code I use

    Code:
    Function Age(Birth_Date,End_Date)
    '***************************************
    ' Works out the age of to the month
    '
    '***************************************
    Dim Months
    Dim Years
    Dim Temp
    'Check if no error
    If IsNull(Birth_Date) or Birth_Date ="" Then
        Age=0.0 
    else
     
        Months = DateDiff("m", CDate(Birth_Date), End_Date)
        Years = Int(Months / 12)
        Temp = Years * 12
        If Years = 0 then Years = "" 
        Age =  Years & "." & Months - Temp
    
    End if
    
    End Function
    in a Query

    Age: age([dob],date())

    it will return

    year.months

    I use it in my HR database to work out when
    a person can have sick days

    only after 6 months .6
    then every year 1.

    must copy code to a module
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  4. #4
    Join Date
    Mar 2003
    Posts
    225
    many thanks worked a treat

Posting Permissions

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