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

    Unanswered: Convert date of birth table into age field

    I am trying to find out how to convert a date of birth field into an age field. If this is not possible how can I create a separate field called "age" which can be updated from the "date of birth" field.

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

  3. #3
    Join Date
    Nov 2004
    Posts
    20

    Reply to date & age field

    I tried this code in the SQL view of a ms access query and it still didn't work. What I have done is created a date of birth field in a table and I want to create a separate field called "age" which can be updated from the "date of birth" field. Is there any methods of achieving this?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what does "it didn't work" mean? syntax error? produced no results? produced the wrong results? crashed the server?

    no, you do not want to have a column for age, because you will then have to go and update that table every single day when someone has a birthday

    just calculate the age on the fly

    if you would be so kind as to display a few sample birthdates, i'll see whether there might have been a typo in my code by testing it for you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2004
    Posts
    20

    dates & age

    19-01-1985
    22-11-2002
    06-09-1935

    here are some sample dates

  6. #6
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    The code from r937 is good code. I copied it directly into a query and changed the "yourtable" to "table3", which is where I put my sample information, and it worked great. I even used Dave_is100's sample data and the query returned 19, 1, and 69. If all goes well, and I expect that it will, the second number will change to 2 tomorrow.

    By the way, I agree with r937 that you shouldn't have an age field in your table since that is a dynamic value. Keep the DOB in the table and let the query calculate the age on the fly any time that you need it.

    TD

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    and here is the query

    and please note, it *does* work

    Code:
    create table Dave_is100 
    ( name varchar(9)
    , birthdate datetime
    )
    insert into Dave_is100 values ('curly', #19-01-1985# )
    insert into Dave_is100 values ('larry', #22-11-2002# )
    insert into Dave_is100 values ('moe', #06-09-1935# )
    
    select * from Dave_is100
    
    name	birthdate
    curly	1985-01-19
    larry	2002-11-22
    moe	1935-06-09
    
    select name
         , year(date()) 
          -year(birthdate)
          -iif(month(date())>month(birthdate),0
          ,iif(month(date())<month(birthdate),1
          ,iif(day(date())<day(birthdate),1,0))) as age
      from Dave_is100     
      
    name	age
    curly	19
    larry	1
    moe	69
    the only possible issue is that access likes to interpret dubious dates like 06-09-1935 as june 9th instead of september 6th, but this can be circumvented by always entering dates in yyyy-mm-dd format
    Last edited by r937; 11-21-04 at 20:44.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2004
    Posts
    20

    age & date

    I have just tried the code in a select query in ms access and in get the following message - "syntax error in create table statement".

    But also, I don't want to create a new table, I just want to use this code to insert into an existing table which already has a "date of birth" column. Is there any way of doing this.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the code i posted was not for you to use in your situation, it was demo code and the purpose was to show you that it does work

    (in access, you would have to run each statement separately)

    it seems you do not understand what we are suggesting -- in order to display people's ages, you do not have to store the age, you just calculate it righ in the query

    so if the table already has a date of birth column, you have nothing to insert
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    What this code is showing you is how to calculate DOB on the fly, after i first saw it ive always used it for DB Tables
    What to do here is actually physically create a table,(as this is easy to do in access) changing the datebirth field into the date/time format
    add some values to it
    then run this code

    Code:
    select name
         , year(date()) 
          -year(birthdate)
          -iif(month(date())>month(birthdate),0
          ,iif(month(date())<month(birthdate),1
          ,iif(day(date())<day(birthdate),1,0))) as age
      from Dave_is100
    this shows how easy it is to get the age on the fly, the only way i can see to hold age within your table would be if that age never changed i.e. age on entry or age on leaving or age at death,
    If the age is to change then the age is dynamic i.e. age today then you would have to update your table on a daily basis or else your data would be inaccurate

    Is there any specific reason why your age field should be in the table rather than in a query?

    Dave
    Last edited by DavidCoutts; 11-22-04 at 10:21.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by DavidCoutts
    What to do here is actually physically create a table
    that is exactly what the code that i posted does

    (and no, it is not sql server code )

    the only thing you must do is paste each statement into the sql view and run it separately, because access does not allow more than one statement to run at a time

    the reason i used access DDL was to demonstrate that i was doing nothing fancy in my table to calculate the age
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Nov 2004
    Posts
    20

    code worked

    the date/age code worked perfectly. I realised that i had to create a new table. Thanks for the code

  13. #13
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Hi Rudy,
    Was Just backing you up, i put the create table code into access and got an Error code,
    Just redid it and it works fine must be still going a bit mad, or had an extra character in or something!
    I deleted the bit about SQL Server as soon as i realised i was wrong ive got it in the brain with the SQL ive been writing over the past few days,

  14. #14
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Dave_is100, dont bother with the new table, just have a query with the info you want and the age calculation rudy provided this you can run at anytime you need and it will be correct

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks david

    he seems determined to create a table with an age collumn, so let's let him go ahead and do that

    he'll be back when he realizes that he has to update it every day!

    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
  •