Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Nov 2004
    Posts
    20

    Unanswered: another age/date thing

    a few years back, i did table which stored peoples date of birth and a query to output their ages. now i want to do something similar

    i want to create a query from the date_of_births table which does the follwoing

    1. Output ages
    2. But instead of using todays date and dob to output ages, i want to use a specific date and dob

    so for example:

    Name, DOB, Age
    David Morgan, 19/01/1985, 19

    using the date "10/09/2004"

    using a query, how do i do this because DateDiff is really pissing me off at the mo!

    help would be much appreciated

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Output ages:
    Put this in "field" as a new column
    Age: DateDiff("yyyy",[birth_date],"10/09/2004")

    - GeorgeV
    George
    Home | Blog

  3. #3
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by Dave_is100
    how do i do this because DateDiff is really pissing me off at the mo!
    why not:
    Code:
    Dim lngDateDiff As Long
    lngDateDiff = DateDiff("yyyy", "19/01/1985", "10/09/2004")
    Me.Geek = True

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    nevermind - someone beat me to it
    Inspiration Through Fermentation

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Lol RNG

    Note: My example can be put in using the query builder while Nick's is in VB
    Take your pick!
    George
    Home | Blog

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Wait a minute....
    I thought I remembered something tricky about this.

    DateDiff("yyyy",#1/19/1985#,#1/9/2004#)

    Returns 19, even though the person is 10 days from his 19th birthday.
    Inspiration Through Fermentation

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ahh correct - because it's only working out the difference between the years.
    Good spot RDG.
    Look what MS Help returns when you search "Age"
    Code:
    =DateDiff("yyyy", [Birthdate], Now())+ Int( Format(now(), "mmdd") < Format( [Birthdate], "mmdd") )
    Obviously replace the Now() with the date you want
    *takes some credit for it anyway*
    George
    Home | Blog

  8. #8
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by georgev
    Ahh correct - because it's only working out the difference between the years.
    *considers self humbled*

    EDIT:
    PS Dave,
    Maybe part of your problem is that there is no 19th month
    Quote Originally Posted by Dave_is100
    Name, DOB, Age
    David Morgan, 19/01/1985, 19
    Last edited by nckdryr; 02-21-07 at 11:37.
    Me.Geek = True

  9. #9
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote Originally Posted by georgev
    Good spot RDG.
    hmmm... I guess sobriety can be useful once in a while.
    Inspiration Through Fermentation

  10. #10
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Quote Originally Posted by nckdryr
    *considers self humbled*

    EDIT:
    PS Dave,
    Maybe part of your problem is that there is no 19th month
    European date syntax
    Inspiration Through Fermentation

  11. #11
    Join Date
    Nov 2004
    Posts
    20
    i'm on the right path, but its just that i want the persons age (from their dob) to be accurate to the actual date in question

    example:

    Name, DOB, Age
    DMorgan, 19/12/1985, 18

    NOT

    DMorgan, 19/12/1985, 19

  12. #12
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by RedNeckGeek
    European date syntax
    *considers self humbled....again*

    Maybe I should look into this "sobriety" thing you speak of


    PS Dave, see george's post above
    Me.Geek = True

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by RedNeckGeek
    hmmm... I guess sobriety can be useful once in a while.
    Are you sure?
    and yeah, Dave, scroll on up ^^
    George
    Home | Blog

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    Ahh correct - because it's only working out the difference between the years.
    george, we've solved this before -- accurate to the day

    did you forget already?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    rudy - i searched and SEARCHED for your SQL on this and couldnt find it.
    give us a link.

    izy
    currently using SS 2008R2

Posting Permissions

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