Question 304695 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL error : 1525. Incorrect DATETIME value: ''

My Query is –

SELECT ins.*, ic.* 
FROM insurance_data AS ins 
LEFT JOIN insurance_companies AS ic ON ins.provider = ic.id 
LEFT JOIN phone_numbers AS ph ON (ic.id = ph.foreign_id AND ph.type = 2) 
WHERE (ins.termination_date IS NULL OR ins.termination_date = "" OR 
       ins.termination_date = "0000-00-00") AND ins.provider IS NOT NULL 
 AND ins.provider > 0 
ORDER BY ins.date DESC LIMIT 1

I have set SQL Modes in MySQL
to "ALLOW_INVALID_DATES,ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION".
But I am still getting error Error Code: 1525. Incorrect DATETIME value: ”.

I am using MySQL ver 8.0.35 on windows 10. I am running query in MySQL workbench.

Tags:

2

Answers


  1. 0

    I modified the query to remove the check for an empty string ('') in the termination_date field, focusing only on NULL and valid dates greater than '0000-00-00'. Hope this helps to fix the error >>

    SELECT ins.*, ic.* 
FROM insurance_data AS ins 
LEFT JOIN insurance_companies AS ic ON ins.provider = ic.id 
LEFT JOIN phone_numbers AS ph ON (ic.id = ph.foreign_id AND ph.type = 2) 
WHERE (ins.termination_date IS NULL OR ins.termination_date > '0000-00-00') 
  AND ins.provider IS NOT NULL 
  AND ins.provider > 0 
ORDER BY ins.date DESC 
LIMIT 1;
    Login or Signup to reply.
  2. 0

    You have disabled strict_trans_mode and enabled ALLOW_INVALID_DATES so empty strings are written to the db as ‘0000-00-00 00:00:00’ but this does not apply to a date check against an empty string

    https://dbfiddle.uk/_b6rynV1

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search