Add leading zero in dates

De TNG_Wiki
Sauter à la navigation Sauter à la recherche

The following query adds a leading zero in dates which look like 1 Jul 1900, it will become 01 Jul 1900; Make backups before you do this. Check out also first if you don't have any faulty dates since this will also change 21Jul 1900 into 021Jul 1900. Search in the report section for the right reports.

Copy and paste the text between the lines into the MySQL section of your MySQL admin, IMPORTANT: Make a backup before you try anything and use this at your own risk!


 UPDATE tng_people SET birthdate = concat("0",birthdate) where length(birthdate) = 10 and YEAR (birthdate) > "999";
UPDATE tng_people SET altbirthdate = concat("0",altbirthdate) where length(altbirthdate) = 10 and YEAR (altbirthdate) > "999";
UPDATE tng_people SET deathdate = concat("0",deathdate) where length(deathdate) = 10 and YEAR (deathdate) > "999";
UPDATE tng_people SET burialdate = concat("0",burialdate) where length(burialdate) = 10 and YEAR (burialdate) > "999";
UPDATE tng_events SET eventdate = concat("0",eventdate) where length(eventdate) = 10 and YEAR (eventdate) > "999";

UPDATE tng_families SET marrdate = concat("0",marrdate) where length(marrdate) = 10 and YEAR (marrdate) > "999";
UPDATE tng_families SET divdate = concat("0",divdate) where length(divdate) = 10 and YEAR (divdate) > "999";

UPDATE tng_people SET birthdate = concat("0",birthdate) where length(birthdate) = 9 and YEAR (birthdate) < "1000;
UPDATE tng_people SET altbirthdate = concat("0",altbirthdate) where length(altbirthdate) = 9 and YEAR (altbirthdate) < "1000;
UPDATE tng_people SET deathdate = concat("0",deathdate) where length(deathdate) = 9 and YEAR (deathdate) < "1000;
UPDATE tng_people SET burialdate = concat("0",burialdate) where length(burialdate) = 9 and YEAR (burialdate)< "1000;
UPDATE tng_events SET eventdate = concat("0",eventdate) where length(eventdate) = 9 and YEAR (eventdate) < "1000;

UPDATE tng_families SET marrdate = concat("0",marrdate) where length(marrdate) = 9 and YEAR (marrdate) < "1000;
UPDATE tng_families SET divdate = concat("0",divdate) where length(divdate) = 9 and YEAR (divdate) < "1000;