Invisible characters in date field

Description: A report which lists people with invisible characters in birth or death dates (this gives all kind of problems)

Copy and paste the text between the lines into the reports section below "OR Leave Display, Criteria and Sort fields blank and enter direct SQL SELECT statement here:" and give it the title you think is appropriate

---

SELECT personID, gedcom, lastname, firstname, birthdate, deathdate FROM  tng_people WHERE NOT ( ( HEX( birthdate ) REGEXP '^([0-7][0-9A-F])*$' )  OR ( HEX( deathdate ) REGEXP '^([0-7][0-9A-F])*$' ) ) ORDER BY  `personID` DESC

---

To solve the problem I copied the true dates into the date field, did that person at a time with the following query

---

UPDATE `tng_people` set birthdate=birthdatetr WHERE  NOT 
(
HEX( birthdate ) REGEXP '^([0-7][0-9A-F])*$'
) and personID = "I704213";
UPDATE `tng_people` set deathdate=deathdatetr WHERE  NOT 
(
HEX( deathdate ) REGEXP '^([0-7][0-9A-F])*$'
) and personID = "I704213";

---

You will have to replace the IDs with your own IDs. You will have to do this one by one since the invisible characters didn't copy the day to the true dates and you don't want to do all your research from scratch. Then; edit the person and copy the day to right fields and the datavalidation script of TNG will correct the date automatically into the right format.