mysql - Two different date types in column -

i trying create age group array in mysql.

select      count(*),     case          when age < 60 '<60'         when age >= 61 , age <= 65 '61-65'         when age >= 66 , age <= 70 '66-70'         when age >= 71 , age <= 75 '71-75'         when age >= 76 , age <= 80 '76-80'         when age > 81  '>81'     end age_group  from(      select year(current_time()) - year(dateborn) age     custs      fdid = 'angl01'  ) custs2  group age_group 

when ran query, worked fine, except there 2013 null results. turns out there 2 data formats in column.

the first on year: 'yyyy'

the second on includes day , month: 'dd/mm/yyyy'

how can modify query take both data formats account?

ideally should store dates using date data type.

given current schema, assuming dates always either in yyyy or dd/mm/yyyy format year 4 rightmost characters, can use right(dateborn,4), this:

select      count(*),     case          when age < 60 '<60'         when age >= 61 , age <= 65 '61-65'         when age >= 66 , age <= 70 '66-70'         when age >= 71 , age <= 75 '71-75'         when age >= 76 , age <= 80 '76-80'         when age > 81  '>81'     end age_group from(      select year(current_date()) - cast(right(dateborn,4) unsigned)  age     custs      fdid = 'angl01' ) custs2 group age_group 

you should check values don't match expected date formats. query give sample of non-conformant rows:

select dateborn custs  dateborn not regexp '^[0-9][0-9][0-9][0-9]$' , dateborn not regexp '^[0-3][0-9]\/[0-1][0-9]\/[0-9][0-9][0-9][0-9]$' limit 25