CODEWITHSUNDEEP
mysql
jazz
jazz

Reputation: 141

How to Check in Mysql either the date is valid or not?

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

Upvotes: 0

Views: 1437

Answers (2)

Nick
Nick

Reputation: 147146

Rather than checking all the parts of the date individually as you are (and what you have won't work as it will still allow November 31 or February 30 as a date), just check to see if MySQL thinks it's a valid date by passing it through DATE and checking for a non-NULL result. So replace

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

with

AND DATE(dob_new) IS NOT NULL

Upvotes: 1

yusher
yusher

Reputation: 226

Is the dob_new type date of mysql? If it is, why don't you use dob_new between '2018-01-01' and '2018-12-31'

Upvotes: 0

Related Questions