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.
| 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/ |
 |
|
|
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 |
|
|
|
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 |
|
|
|
|
|
|
calculate age when DOB is null