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 AgeAnd this obviously returns an errorisnull(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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2015-04-21 : 05:51:58
|
| Isfloor(datediff(day, DOB, CURRENT_TIMESTAMP) / 365.25) as Ageaccurate? 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