Birth Year Mis-Match #date #birth

Lists individuals whose Birth Year is missing from the sidebar (and other views and reports where just the YEAR is outputted) or mismatches the value that has been stored in the Date field of the Birth fact. This is less of an issue as of RM, which incorporates an update of the BirthYear and DeathYear fields in the NameTable under the Rebuild Indexes tool in the menu File > Database Tools. However, the utility is still useful in drawing attention to where there are multiple Birth facts and where more than one is marked Primary.

New: see Rebuild Indexes and Update Birth and Death Years for a batch procedure to match up the Birth and Death years with their facts.
Download latest: BirthYearMisMatch.sqlRMtrix_tiny_check.png
Ver 4 corrects an overcount of Birth facts due to alternate names.
Ver 3 reports number of Birth facts per individual and suppresses facts with ‘UNKNOWN’ in the Date field.
Ver 2 added checking for years before 1000.

SQLite Developer screen shot from Ver 1.

This sql query uses some sqlite core functions to measure string length, extract a sub-string and compare to another string.

  1. In the above screenshot from the original version of the query, all the Birth Years shown are 0. The Date field could be worked on in RM4’s Edit Person dialog to change from free text (stored with a ‘T’ prefix) to a recognisable date format (stored with a ‘D’ prefix) and that should update the Birth Year.
  2. In the case of multiple Birth Facts for an individual, it is the last one saved that sets the Birth Year; any other Birth Fact not matching the Birth Year will be detected. The query could be modified to suppress reporting a mis-matched Birth Fact when another for that individual does match but it was thought to be useful to draw attention to the mis-match for review and possible correction or deletion.
  3. For RIN 681, a valid date format is stored for Jan 17 in the year 0! The mismatch that was detected compared ‘0’ to ‘0000’. The original version of the query reported a mismatch for all years <1000; later versions do not.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.