• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 421
  • Last Modified:

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
0
CloudApps
Asked:
CloudApps
1 Solution
 
dsackerContract ERP Admin/ConsultantCommented:
DayOfYear is not a function (unless you wrote one). Use this definition for your computed column:

CASE
    WHEN DATEPART(dayofyear, BirthDate) < DATEPART(dayofyear, GETDATE())
         THEN DATEDIFF(YEAR, BirthDate, GETDATE()) - 1
    ELSE DATEDIFF(YEAR, BirthDate, GETDATE())
END

Open in new window

I separated it out for readability (and to verify it worked). Return it to one line or however you wish to lower-case it.
0
 
sammySeltzerCommented:
select DATEDIFF(dayofyear, BirthDate, getdate()) -1 

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I presume dayofyear is a customer function, in which case you have to prefix it with the functions' owner name:

dbo.dayofyear(...)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
sammySeltzerCommented:
Is dayofyear a sql server 2012 function - a new feature in sql server?
0
 
Kevin CrossChief Technology OfficerCommented:
You may find this thread helpful:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23808219.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
0
 
CloudAppsOwnerAuthor Commented:
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now