al4629740
asked on
add computed column from birthdate and time of registration column
I have a birtdate column in date format in my table named tblJJDP. I need to create a computed column (AgeReg) that automatically calculates the birthdate and the Date of Registration (DOR) column.
So if the person was born on 1/1/00 and they were registered on 1/1/12, then the AgeReg column should show 12 years old.
Column names are
DOR - date type
Birthdate -date type
AgeReg - integer type
So if the person was born on 1/1/00 and they were registered on 1/1/12, then the AgeReg column should show 12 years old.
Column names are
DOR - date type
Birthdate -date type
AgeReg - integer type
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
How do I output the results to the AgeReg column?
ALTER TABLE tblJJDP ADD AgeReg AS DATEDIFF(YEAR, BirthDate, DOR) -
CASE WHEN CONVERT(char(5), BirthDate, 101) > CONVERT(char(5), DOR, 101) THEN 1 ELSE 0 END
CASE WHEN CONVERT(char(5), BirthDate, 101) > CONVERT(char(5), DOR, 101) THEN 1 ELSE 0 END
Scott has covered this more than once so I will not repeat here, but here is one of his solutions:
https://www.experts-exchange.com/questions/28090389/What-query-would-I-run-in-SQL-Server-2008-R2-to-calculate-the-age-of-each-subject.html
If that is indeed what you want to use then it is trivial to convert that to a computed column.