Forums: » Register « |  Free Tools |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support |

New Free Tools on Dev Shed!

#1
September 17th, 2013, 01:32 PM
 JimD356
Registered User

Join Date: Sep 2013
Posts: 3
Time spent in forums: 24 m 4 sec
Reputation 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
September 17th, 2013, 02:03 PM
 kicken
Wiser? Not exactly.

Join Date: May 2001
Location: Bonita Springs, FL
Posts: 5,784
Time spent in forums: 2 Months 2 Weeks 4 Days 1 h 27 m 57 sec
Reputation Power: 3716
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 out out, show some love with some reputation, or tip with Bitcoins to 1N645HfYf63UbcvxajLKiSKpYHAq2Zxud

#3
September 17th, 2013, 03:48 PM
 r937
SQL Consultant

Join Date: Feb 2003
Posts: 26,804
Time spent in forums: 3 Months 1 Week 4 Days 6 h 53 m 3 sec
Reputation Power: 4208
Quote:
 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

Quote:
 Originally Posted by JimD356 The start of term date is 01/08/YEAR, ...
is that january 8th or august 1st ???
__________________
r937.com | rudy.ca

#4
September 17th, 2013, 04:26 PM
 JimD356
Registered User

Join Date: Sep 2013
Posts: 3
Time spent in forums: 24 m 4 sec
Reputation Power: 0
Calculating age using DOB and variable year

Quote:
 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
September 17th, 2013, 04:30 PM
 JimD356
Registered User

Join Date: Sep 2013
Posts: 3
Time spent in forums: 24 m 4 sec
Reputation Power: 0
Quote:
 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.

 Viewing: Dev Shed Forums > Databases > MS SQL Development > Calculating age using DOB and variable year