Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 calculate age when DOB is null

Author  Topic 

BillEdd
Starting Member

12 Posts

Posted - 2015-04-20 : 17:07:03
What is the best way, or a good way, or even a way to calculate age using a DOB field when the DOB field is sometimes NULL so that the result does not return NULL? Would prefer to just return an empty string.

I have this, which works, but returns NULL when DOB field is NULL.
floor(datediff(day, DOB, CURRENT_TIMESTAMP) / 365.25) as Age

And this obviously returns an error
isnull(floor(datediff(day, c.DOB, CURRENT_TIMESTAMP) / 365.25),'') as Age.

Thanks for your help. I prefer not to write a function.

Bill

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-20 : 17:08:47
You can't return an empty string when the non-null values return an integer. You can convert to varchar, but you should just return a NULL and instead handle the NULL in the application.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-04-21 : 05:51:58
Is

floor(datediff(day, DOB, CURRENT_TIMESTAMP) / 365.25) as Age

accurate? It reports a year-early on the DAY of their birthday (perhaps only for people with a birthday after 01-March), for example
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2015-04-21 : 09:17:49
A quick (and accurate) way of calculating the age is in this post. See the responses by sunitabeck and madhivanan. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=181713
Go to Top of Page
   

- Advertisement -