asked on

SQL Server Computed Column Specification for Age

Can anyone see my syntax error?

I get following message: Error validating the formula for column 'Age'.

(case when dayofyear([BirthDate]) < dayofyear(getdate()) then datediff(year,[BirthDate],getdate())-1 else datediff(year,[BirthDate],getdate()) end)

Thanks,
ScreenShot.pdf

ASKER CERTIFIED SOLUTION

dsacker

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sammySeltzer

select DATEDIFF(dayofyear, BirthDate, getdate()) -1

Open in new window

Guy Hengel [angelIII / a3]

I presume dayofyear is a customer function, in which case you have to prefix it with the functions' owner name:

dbo.dayofyear(...)

sammySeltzer

Is dayofyear a sql server 2012 function - a new feature in sql server?

Kevin Cross

You may find this thread helpful:
https://www.experts-exchange.com/questions/23808219/Calculating-an-age-field-from-a-visitdate-field-and-a-date-of-birth-field.html

Scott's approach is similar to dsacker's above, except he eliminated the repetition of the DATEDIFF by putting the CASE on the subtraction.

In other words, you can do this:
DATEDIFF(YEAR, BirthDate, GETDATE()) - CASE WHEN DATEPART(dayofyear, BirthDate) < DATEPART(dayofyear, GETDATE()) THEN 1 ELSE 0 END

CloudApps

ASKER

dsacker,

Thanks for figuring out what I was trying to accomplish. Your syntax worked to solve the problem.

Sorry, to very one else for the poor presentation of my problem.