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,424
    Provided Answers: 8
    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

    See clear as mud


    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 based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  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
  •