#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    3
    Rep Power
    0

    Calculating age using DOB and variable year


    Hello,

    I am trying to replace a hard-coded 'start of term' date with a variable date, in a simple existing query.

    The query calculates a student's age (in years), based on the time difference between the student's date of birth, and the current start of term date.

    The start of term date is 01/08/YEAR, and obviously the YEAR value changes each year, so this year, start of term date would be 01/08/2013.

    I can get the current year by using SELECT year(GETDATE()), and I can do the age calculation to the current date, using this :

    Select FLOOR(DATEDIFF(day,'1996/09/26',GETDATE())/365.242199), but I can't figure out how to express the start of term date as a variable year/fixed day/fixed month.

    So, the question is : how to combine the variable YEAR with the fixed day and fixed month, so that it can be used in a DATEDIFF expression?

    Many thanks!
  2. #2
  3. Wiser? Not exactly.
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    May 2001
    Location
    Bonita Springs, FL
    Posts
    5,932
    Rep Power
    4033
    Something like this:
    Code:
    Select FLOOR(DATEDIFF(day,'1996/09/26',CONVERT(VARCHAR,YEAR(GETDATE()))+'/01/08')/365.242199)
    Recycle your old CD's, don't just trash them



    If I helped you out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by JimD356
    The query calculates a student's age (in years), based on the time difference between the student's date of birth, and the current start of term date.
    is dividing by 365.25 really good enough for you?

    what about the guy who was born 1993-10-01... he's currently 19 but he won't turn 20 for another two weeks

    Originally Posted by JimD356
    The start of term date is 01/08/YEAR, ...
    is that january 8th or august 1st ???
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    3
    Rep Power
    0

    Calculating age using DOB and variable year


    Originally Posted by kicken
    Something like this:
    Code:
    Select FLOOR(DATEDIFF(day,'1996/09/26',CONVERT(VARCHAR,YEAR(GETDATE()))+'/01/08')/365.242199)
    kicken - thanks for the code. It works fine, but I had to change the '/01/08' to '/08/01' (MM/DD)

    I should have been clearer about the date format (UK) in my original post. Thanks again!
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2013
    Posts
    3
    Rep Power
    0
    Originally Posted by r937
    is dividing by 365.25 really good enough for you?

    what about the guy who was born 1993-10-01... he's currently 19 but he won't turn 20 for another two weeks

    is that january 8th or august 1st ???
    It's August 1st. Sorry, I should have made that clear in my original post.

    As for the guy who was born on 1993-10-01 - yes, but the measurement is from date of birth to the start of term date of current year (1st Aug 'YEAR'). Again, the confusion is my fault for not being specific enough.

    Thanks for your post!

IMN logo majestic logo threadwatch logo seochat tools logo