Link to home
Create AccountLog in
Avatar of goldmineexpert
goldmineexpert

asked on

adding age to SQL query column

i would like to calculate the age in a column next to the date column

1954    58
Avatar of ChetOS82
ChetOS82
Flag of United States of America image

SELECT YEAR(datecolumn), FLOOR(DATEDIFF(day,datecolumn,GETDATE())/365.242199)
Avatar of Ashok
never mind
The expression in http:#a38139048 may be unreliable.  For example, suppose you have someone born on 2010-06-30, and you measured the age on 2011-06-30.

Most everyone would agree that the answer should be 1 year old, but the expression above will yield 0.
ASKER CERTIFIED SOLUTION
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SELECT (year(gatedate())-year column) as age

or else if iyou have date column than

SELECT (year(gatedate())-year(datecolumn)) as age
keyu,

You do realize that that approach can give you a result of one year old when the difference in dates can be as little as one day, right?

Patrick
yah sorry my mistake..go for this..


SELECT (year(gatedate())-year_column) as age

or else if iyou have date column than

SELECT (year,datecolumn,gatedate()) as age
keyu,

Please check your solution before posting.  Patrick has already pointed out once why your solution cannot be used.  Now you have posted it again and it does not even compile, let alone get the right result.
I would definitely not like to see this solution returned by someone searching for a similar problem.  Both mine and Matthewspatrick's solutions were correct and he even attempted to explain why the accepted solution was not a good one.  I would vote for one of the truly correct answers to get marked as the accepted solution or the question deleted entirely so it doesn't confuse anyone later.
I would vote for one of the truly correct answers to get marked as the accepted solution or the question deleted entirely so it doesn't confuse anyone later.
I agree.  Points should be awarded here http:#a38139144 and here http:a#38140055
bymistake datedif removed ... but questioner got the idea so according to me just minor correction is required here..for future questioner...

if that idea works for the applicant i dont find anything wrong with it..

SELECT datediff(year,datecolumn,gatedate()) as age

anyways...you can proceed further what ever you feel right about this..
so according to me just minor correction is required here..for future questioner...
No.  It clearly illustrates a fundamental error in your perception of how DATEDIFF actually works.  It also, shows that you are unprepared to even test the code you have posted not once, but three times or you would know that at the very least it does not even compile.