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.