i want to ask about Mysql (date)

i have a set of data that has a date using data type varchar
so i want to update the real table ,but in the real table the data type is date
when i want to update , it show error like this 

> 
> Error Code: 1292
>  Incorrect date value: '1975-11-31' for column 'dob' at row 1
> 
> 
> 

and i realize that the date in not valid...
so how supposedly i get the valid date only?

please help me..

this is my query

```
UPDATE ori_table a      
INNER JOIN set_of_data dd ON dd.old_id = a.id   
SET a.dob = dd.dob_new   

WHERE a.id IN (SELECT old_id FROM set_of_data    
WHERE status_color = 0    
AND dob_new IS NOT NULL   
AND age != '#VALUE!'   
AND SUBSTRING(dob_new ,6,2) <= 12   
AND SUBSTRING(dob_new ,6,2) >= 01   
AND SUBSTRING(dob_new ,1,4) <= 2018   
AND SUBSTRING(dob_new ,9,2) <= 31   
AND SUBSTRING(dob_new ,9,2) >= 01)   

AND a.dob IS NULL

```