Need to calculate age using TIMESTAMPDIFF() function based on year of birth but some row return null-CodePudding

Please someone help me to check my sql statement. I want to calculate people age based on year but there are some row not return the actual value. but null. Pardon my english. Hope you guys understand my question. This is my sql statement :

select no_id,
    case when substring(no_id,1,2) <Substring(YEAR(NOW()),-2) 
    then Concat('20',substring(no_id,1,2),'-'  ,substring(no_id,3,2),'-', substring(no_id,5,2))
    else
     Concat('19',substring(no_id,1,2),'-'  ,substring(no_id,3,2),'-', substring(no_id,5,2))
    end as dob, TIMESTAMPDIFF(YEAR, no_id, CURDATE()) AS age
from driver_details;

my actual table input like this:

no_id	category_license	status
980401001121	D	1
970101110101	D	1

my output after I run the sql statement in phpmyadmin:

no_id	dob	age
980401001121	1998-04-01	24
970101110101	1997-01-01	NULL

This is expecetd value that I want to return in my table in database.

no_id	dob	age
980401001121	1998-04-01	24
970101110101	1997-01-01	25

CodePudding user response：

The problem is that you want to use TIMESTAMPDIFF on the entire no_id. This is incorrect because this would only work correctly if the string represents a valid datetime.

This is not the case for your samples like 840312135169 because the "69" at the end would be read as seconds, but a minute has 60 seconds only.

So you need to either provide a valid datetime in this column or you only run TIMESTAMPDIFF on the first part of your column which is a valid date and ignore the rest:

select no_id,
case when substring(no_id,1,2) <Substring(YEAR(NOW()),-2) 
  then Concat('20',substring(no_id,1,2),'-'  ,
    substring(no_id,3,2),'-', substring(no_id,5,2))
  else Concat('19',substring(no_id,1,2),'-'  ,
    substring(no_id,3,2),'-', substring(no_id,5,2)) 
end as dob,  
TIMESTAMPDIFF(YEAR, substring(no_id,1,6), CURDATE()) AS age -- changed here
from driver_license;

See db<>fiddle