Thread: Calculating age using DOB and variable year

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

Join Date
Sep 2013
Posts
4
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. Something like this:
Code:
`Select FLOOR(DATEDIFF(day,'1996/09/26',CONVERT(VARCHAR,YEAR(GETDATE()))+'/01/08')/365.242199)`
3. 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 ???
4. No Profile Picture
Registered User
Devshed Newbie (0 - 499 posts)

Join Date
Sep 2013
Posts
4
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!
5. No Profile Picture
Registered User
Devshed Newbie (0 - 499 posts)

Join Date
Sep 2013
Posts
4
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.