MySQL Age from Date of Birth (ignore nulls)

Question

I am trying to calculate age from Date of Birth, which I was able to do successfully using this thread. However, some of my DateOfBirth have null values, and using my below formula, the result coms back as "2012" instead of (blank/null).

Here is my table:

10/06/1990
01/09/1998
*null*
*null*
02/16/1991

Here is my desired result:

23
25
(blank)
(blank)
22

Here is my formula so far:

year(curdate())-year(user.DateOfBirth) - (dayofyear(curdate()) < dayofyear(user.DateOfBirth)) AS 'Age'

Here is what I'm actually getting:

Here are a couple of things I've tried to eliminate the "2012", which results in some encrypted text:

IF(user.DateOfBirth > '0001-01-01',AboveFormula,'')
CASE AboveFormula WHEN 2012 THEN '' ELSE AboveFormula END AS 'Age'

NULLs are not stored in the field. NULL is a hidden status bit elsewhere in the record. That is why a special mechanism must be used to test whether a field is NULL: `DateOfBirth IS NOT NULL` or `DateOfBirth IS NULL`. — wallyk, Dec 23 '13 at 20:29

score 4 · Accepted Answer · answered Dec 23 '13 at 20:14

4

Try this:

SELECT  CASE
            WHEN user.DateOfBirth IS NULL THEN ""
            ELSE year(curdate())-year(user.DateOfBirth) - (dayofyear(curdate()) < dayofyear(user.DateOfBirth)) 
        END AS 'Age'
FROM    myTable

answered Dec 23 '13 at 20:14

Anil

2,539
6
33
42

This results in encrypted text, like 0x32303132 or 0x3233 (using LinqPad) – Ken Dec 23 '13 at 20:15
Seems to work just fine here: http://sqlfiddle.com/#!2/0177e/2/0 I added an ID column in the sqlfiddle just to delineate the rows better. – Anil Dec 23 '13 at 20:27

Sujith Surendranathan · Answer 2 · 2013-12-23T20:24:29.170

2

SELECT 
CASE
WHEN DateOfBirth IS NULL THEN ""
ELSE 
DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(DateOfBirth , '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(DateOfBirth , '00-%m-%d')) 
END AS age
FROM    myTable

edited Dec 23 '13 at 20:24

answered Dec 23 '13 at 20:18

Sujith Surendranathan

2,569
17
21

I think the OP wanted `IS NULL THEN ''` or a blank result in the case of missing data. – O. Jones Dec 23 '13 at 20:22

score 1 · Answer 3 · answered Dec 23 '13 at 21:01

1

SELECT IF(user.DateOfBirth IS NULL,"",
          YEAR(CURDATE()) - YEAR(user.DateOfBirth)
          - IF(DAYOFYEAR(CURDATE()) < DAYOFYEAR(user.DateOfBirth),1,0)
         ) as 'Age'
FROM user

answered Dec 23 '13 at 21:01

Tin Tran

6,194
3
19
34

MySQL Age from Date of Birth (ignore nulls)

3 Answers3