SQL Server Computed Column Specification for Age

Posted on 2014-01-02
Last Modified: 2014-01-02
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)

Question by:CloudApps
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 20

Accepted Solution

dsacker earned 500 total points
ID: 39751765
DayOfYear is not a function (unless you wrote one). Use this definition for your computed column:

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

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.
LVL 28

Expert Comment

ID: 39751777
select DATEDIFF(dayofyear, BirthDate, getdate()) -1 

Open in new window

LVL 143

Expert Comment

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

LVL 28

Expert Comment

ID: 39751815
Is dayofyear a sql server 2012 function - a new feature in sql server?
LVL 59

Expert Comment

by:Kevin Cross
ID: 39751908
You may find this thread helpful:

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

Author Closing Comment

ID: 39751963

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.

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question