SQL Table valued function Age

Question

I am trying to construct a tvf for calculating age. Currently, I am working with this code, which brings up a few questions.

Code:

IF ( MONTH (GETDATE()) = (SELECT MONTH (ISNULL (PERS_DateBirth, '1900-01-01')) 
                          FROM T_Person) 
     AND DAY (GETDATE()) >= (SELECT DAY (ISNULL(PERS_DateBirth, '1900-01-01')) 
                             FROM T_Person)
     OR MONTH (GETDATE()) > (SELECT MONTH (ISNULL (PERS_DateBirth, '1900-01-01')) 
                             FROM T_Person)
   )
SELECT YEAR (GETDATE()) - YEAR (ISNULL (PERS_DateBirth, '1900-01-01')) 
FROM T_Person
ELSE SELECT YEAR (GETDATE()) - YEAR (ISNULL (PERS_DateBirth, '1900-01-01')) - 1  
     FROM T_Person

Questions:

Subquery returned more than 1 value. How is that possible, considering the code above?
Can anyone help me in rewriting this, so that it can be a tvf, maybe using CASE WHEN.

PS. I prefer the construct / logic as above, over DATEDIFF constructions using 365,25 days and so on.

Thanks all for helping out, if the code above is unclear, please let me know.

`SELECT YEAR (GETDATE()) ... FROM T_Person` ... why can't this subquery return more than one row? There is no `WHERE` clause or restrictions of any other kind preventing it. — Tim Biegeleisen, Oct 05 '17 at 07:16
@TimBiegeleisen you point out that you see what is wrong. Would be extra helpful if you would suggest code that does work. — SQL_M, Oct 05 '17 at 08:04
I don't know your data, so I can't suggest anything. Checking those subqueries should be your first step. — Tim Biegeleisen, Oct 05 '17 at 08:09
@SQL_M if you post sample data and expected result (in tabular text format) it would be easier to try to help you. — etsa, Oct 05 '17 at 08:32
I must be missing something, but what's the problem with SELECT DATEDIFF(YY, PERS_DateBirth, GETDATE()) FROM T_Person ? — Raven-Blue Dragon, Oct 05 '17 at 11:12
take a look at https://stackoverflow.com/questions/1572110/how-to-calculate-age-in-years-based-on-date-of-birth-and-getdate — Steve Ford, Oct 05 '17 at 11:45
Thanks @SteveFord. The answer provided by DotJoe is brilliant. — SQL_M, Oct 05 '17 at 13:02

SQL Table valued function Age

0 Answers0