Invalid Dates

From TNG_Wiki
Jump to navigation Jump to search

The following report is meant to trace invalid dates, it will give false positives for 3 and 2 digit years and also for B.C. years. You will have to make a different report for each of the invalid dates you're looking for.


Copy and paste the text between the equal sign 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


/*=============================*/
/* invalid birthdates */
SELECT gedcom, personID, lnprefix, lastname, firstname, birthdate, birthdatetr, changedby
FROM tng_people
WHERE STR_TO_DATE( birthdate, '%d %b %Y' ) IS NULL
AND STR_TO_DATE( birthdate, '%b %Y' ) IS NULL
AND STR_TO_DATE( birthdate, '%Y' ) IS NULL
AND NOT (birthdate = "y" or birthdate = "n"
) and NOT (birthdate like "Abt %" and length (birthdate) = 8 and year(birthdatetr) != 0000)
and NOT (birthdate like "Cal %" and length (birthdate) = 8 and year(birthdatetr) != 0000)
and NOT (birthdate like "Est %" and length (birthdate) = 8 and year(birthdatetr) != 0000)
and NOT (birthdate like "Bef %" and length (birthdate) = 8 and year(birthdatetr) != 0000)
and NOT (birthdate like "Abt %" and length (birthdate) = 8 and year(birthdatetr) != 0000)
and NOT (birthdate like "Aft %" and length (birthdate) = 8 and year(birthdatetr) != 0000)

and NOT (birthdate like "Abt %" and length (birthdate) = 7 and year(birthdatetr) != 0000)
and NOT (birthdate like "Cal %" and length (birthdate) = 7 and year(birthdatetr) != 0000)
and NOT (birthdate like "Est %" and length (birthdate) = 7 and year(birthdatetr) != 0000)
and NOT (birthdate like "Bef %" and length (birthdate) = 7 and year(birthdatetr) != 0000)
and NOT (birthdate like "Abt %" and length (birthdate) = 7 and year(birthdatetr) != 0000)
and NOT (birthdate like "Aft %" and length (birthdate) = 7 and year(birthdatetr) != 0000)

and NOT (birthdate like "Abt %" and length (birthdate) = 12 and year(birthdatetr) != 0000 and month(birthdatetr) != 00)
and NOT (birthdate like "Cal %" and length (birthdate) = 12 and year(birthdatetr) != 0000 and month(birthdatetr) != 00)
and NOT (birthdate like "Est %" and length (birthdate) = 12 and year(birthdatetr) != 0000 and month(birthdatetr) != 00)
and NOT (birthdate like "Bef %" and length (birthdate) = 12 and year(birthdatetr) != 0000 and month(birthdatetr) != 00)
and NOT (birthdate like "Aft %" and length (birthdate) = 12 and year(birthdatetr) != 0000 and month(birthdatetr) != 00)

and NOT (birthdate like "Abt %" and length (birthdate) = 15 and year(birthdatetr) != 0000 and month(birthdatetr) != 00 and day(birthdate) != 00)
and NOT (birthdate like "Cal %" and length (birthdate) = 15 and year(birthdatetr) != 0000 and month(birthdatetr) != 00 and day(birthdate) != 00)
and NOT (birthdate like "Est %" and length (birthdate) = 15 and year(birthdatetr) != 0000 and month(birthdatetr) != 00 and day(birthdate) != 00)
and NOT (birthdate like "Bef %" and length (birthdate) = 15 and year(birthdatetr) != 0000 and month(birthdatetr) != 00 and day(birthdate) != 00)
and NOT (birthdate like "Aft %" and length (birthdate) = 15 and year(birthdatetr) != 0000 and month(birthdatetr) != 00 and day(birthdate) != 00)

and NOT (birthdate like "Bet %" and length (birthdate) = 17 and (year(birthdatetr) != 0000 or month(birthdatetr) = 00 or day(birthdatetr) = 00))
and NOT (birthdate like "Bet %" and length (birthdate) = 31 and (year(birthdatetr) != 0000 or month(birthdatetr) = 00 or day(birthdatetr) = 00))


ORDER BY `tng_people`.`birthdate` ASC


/*==================================*/
/* invalid deathdates */

SELECT gedcom, personID, lnprefix, lastname, firstname, deathdate, deathdatetr, changedby
FROM tng_people
WHERE STR_TO_DATE( deathdate, '%d %b %Y' ) IS NULL
AND STR_TO_DATE( deathdate, '%b %Y' ) IS NULL
AND STR_TO_DATE( deathdate, '%Y' ) IS NULL
AND NOT (deathdate = "y" or deathdate = "n"
) and NOT (deathdate like "Abt %" and length (deathdate) = 8 and year(deathdatetr) != 0000)
and NOT (deathdate like "Cal %" and length (deathdate) = 8 and year(deathdatetr) != 0000)
and NOT (deathdate like "Est %" and length (deathdate) = 8 and year(deathdatetr) != 0000)
and NOT (deathdate like "Bef %" and length (deathdate) = 8 and year(deathdatetr) != 0000)
and NOT (deathdate like "Abt %" and length (deathdate) = 8 and year(deathdatetr) != 0000)
and NOT (deathdate like "Aft %" and length (deathdate) = 8 and year(deathdatetr) != 0000)

and NOT (deathdate like "Abt %" and length (deathdate) = 7 and year(deathdatetr) != 0000)
and NOT (deathdate like "Cal %" and length (deathdate) = 7 and year(deathdatetr) != 0000)
and NOT (deathdate like "Est %" and length (deathdate) = 7 and year(deathdatetr) != 0000)
and NOT (deathdate like "Bef %" and length (deathdate) = 7 and year(deathdatetr) != 0000)
and NOT (deathdate like "Abt %" and length (deathdate) = 7 and year(deathdatetr) != 0000)
and NOT (deathdate like "Aft %" and length (deathdate) = 7 and year(deathdatetr) != 0000)

and NOT (deathdate like "Abt %" and length (deathdate) = 12 and year(deathdatetr) != 0000 and month(deathdatetr) != 00)
and NOT (deathdate like "Cal %" and length (deathdate) = 12 and year(deathdatetr) != 0000 and month(deathdatetr) != 00)
and NOT (deathdate like "Est %" and length (deathdate) = 12 and year(deathdatetr) != 0000 and month(deathdatetr) != 00)
and NOT (deathdate like "Bef %" and length (deathdate) = 12 and year(deathdatetr) != 0000 and month(deathdatetr) != 00)
and NOT (deathdate like "Aft %" and length (deathdate) = 12 and year(deathdatetr) != 0000 and month(deathdatetr) != 00)

and NOT (deathdate like "Abt %" and length (deathdate) = 15 and year(deathdatetr) != 0000 and month(deathdatetr) != 00 and day(deathdate) != 00)
and NOT (deathdate like "Cal %" and length (deathdate) = 15 and year(deathdatetr) != 0000 and month(deathdatetr) != 00 and day(deathdate) != 00)
and NOT (deathdate like "Est %" and length (deathdate) = 15 and year(deathdatetr) != 0000 and month(deathdatetr) != 00 and day(deathdate) != 00)
and NOT (deathdate like "Bef %" and length (deathdate) = 15 and year(deathdatetr) != 0000 and month(deathdatetr) != 00 and day(deathdate) != 00)
and NOT (deathdate like "Aft %" and length (deathdate) = 15 and year(deathdatetr) != 0000 and month(deathdatetr) != 00 and day(deathdate) != 00)

and NOT (deathdate like "Bet %" and length (deathdate) = 17 and (year(deathdatetr) != 0000 or month(deathdatetr) = 00 or day(deathdatetr) = 00))
and NOT (deathdate like "Bet %" and length (deathdate) = 31 and (year(deathdatetr) != 0000 or month(deathdatetr) = 00 or day(deathdatetr) = 00))


ORDER BY `tng_people`.`deathdate` ASC

/*====================================*/
/* invalid baptismal dates */

SELECT gedcom, personID, lnprefix, lastname, firstname, altbirthdate, altbirthdatetr, changedby
FROM tng_people
WHERE STR_TO_DATE( altbirthdate, '%d %b %Y' ) IS NULL
AND STR_TO_DATE( altbirthdate, '%b %Y' ) IS NULL
AND STR_TO_DATE( altbirthdate, '%Y' ) IS NULL
AND NOT (altbirthdate = "y" or altbirthdate = "n"
) and NOT (altbirthdate like "Abt %" and length (altbirthdate) = 8 and year(altbirthdatetr) != 0000)
and NOT (altbirthdate like "Cal %" and length (altbirthdate) = 8 and year(altbirthdatetr) != 0000)
and NOT (altbirthdate like "Est %" and length (altbirthdate) = 8 and year(altbirthdatetr) != 0000)
and NOT (altbirthdate like "Bef %" and length (altbirthdate) = 8 and year(altbirthdatetr) != 0000)
and NOT (altbirthdate like "Abt %" and length (altbirthdate) = 8 and year(altbirthdatetr) != 0000)
and NOT (altbirthdate like "Aft %" and length (altbirthdate) = 8 and year(altbirthdatetr) != 0000)

and NOT (altbirthdate like "Abt %" and length (altbirthdate) = 7 and year(altbirthdatetr) != 0000)
and NOT (altbirthdate like "Cal %" and length (altbirthdate) = 7 and year(altbirthdatetr) != 0000)
and NOT (altbirthdate like "Est %" and length (altbirthdate) = 7 and year(altbirthdatetr) != 0000)
and NOT (altbirthdate like "Bef %" and length (altbirthdate) = 7 and year(altbirthdatetr) != 0000)
and NOT (altbirthdate like "Abt %" and length (altbirthdate) = 7 and year(altbirthdatetr) != 0000)
and NOT (altbirthdate like "Aft %" and length (altbirthdate) = 7 and year(altbirthdatetr) != 0000)

and NOT (altbirthdate like "Abt %" and length (altbirthdate) = 12 and year(altbirthdatetr) != 0000 and month(altbirthdatetr) != 00)
and NOT (altbirthdate like "Cal %" and length (altbirthdate) = 12 and year(altbirthdatetr) != 0000 and month(altbirthdatetr) != 00)
and NOT (altbirthdate like "Est %" and length (altbirthdate) = 12 and year(altbirthdatetr) != 0000 and month(altbirthdatetr) != 00)
and NOT (altbirthdate like "Bef %" and length (altbirthdate) = 12 and year(altbirthdatetr) != 0000 and month(altbirthdatetr) != 00)
and NOT (altbirthdate like "Aft %" and length (altbirthdate) = 12 and year(altbirthdatetr) != 0000 and month(altbirthdatetr) != 00)

and NOT (altbirthdate like "Abt %" and length (altbirthdate) = 15 and year(altbirthdatetr) != 0000 and month(altbirthdatetr) != 00 and day(altbirthdate) != 00)
and NOT (altbirthdate like "Cal %" and length (altbirthdate) = 15 and year(altbirthdatetr) != 0000 and month(altbirthdatetr) != 00 and day(altbirthdate) != 00)
and NOT (altbirthdate like "Est %" and length (altbirthdate) = 15 and year(altbirthdatetr) != 0000 and month(altbirthdatetr) != 00 and day(altbirthdate) != 00)
and NOT (altbirthdate like "Bef %" and length (altbirthdate) = 15 and year(altbirthdatetr) != 0000 and month(altbirthdatetr) != 00 and day(altbirthdate) != 00)
and NOT (altbirthdate like "Aft %" and length (altbirthdate) = 15 and year(altbirthdatetr) != 0000 and month(altbirthdatetr) != 00 and day(altbirthdate) != 00)

and NOT (altbirthdate like "Bet %" and length (altbirthdate) = 17 and (year(altbirthdatetr) != 0000 or month(altbirthdatetr) != 00 or day(altbirthdatetr) != 00))
and NOT (altbirthdate like "Bet %" and length (altbirthdate) = 31 and (year(altbirthdatetr) != 0000 or month(altbirthdatetr) != 00 or day(altbirthdatetr) != 00))


ORDER BY `tng_people`.`altbirthdate` ASC


/*==============================================*/
/* invalid burial dates */

SELECT gedcom, personID, lnprefix, lastname, firstname, burialdate, burialdatetr, changedby
FROM tng_people
WHERE STR_TO_DATE( burialdate, '%d %b %Y' ) IS NULL
AND STR_TO_DATE( burialdate, '%b %Y' ) IS NULL
AND STR_TO_DATE( burialdate, '%Y' ) IS NULL
AND NOT (burialdate = "y" or burialdate = "n"
) and NOT (burialdate like "Abt %" and length (burialdate) = 8 and year(burialdatetr) != 0000)
and NOT (burialdate like "Cal %" and length (burialdate) = 8 and year(burialdatetr) != 0000)
and NOT (burialdate like "Est %" and length (burialdate) = 8 and year(burialdatetr) != 0000)
and NOT (burialdate like "Bef %" and length (burialdate) = 8 and year(burialdatetr) != 0000)
and NOT (burialdate like "Abt %" and length (burialdate) = 8 and year(burialdatetr) != 0000)
and NOT (burialdate like "Aft %" and length (burialdate) = 8 and year(burialdatetr) != 0000)

and NOT (burialdate like "Abt %" and length (burialdate) = 7 and year(burialdatetr) != 0000)
and NOT (burialdate like "Cal %" and length (burialdate) = 7 and year(burialdatetr) != 0000)
and NOT (burialdate like "Est %" and length (burialdate) = 7 and year(burialdatetr) != 0000)
and NOT (burialdate like "Bef %" and length (burialdate) = 7 and year(burialdatetr) != 0000)
and NOT (burialdate like "Abt %" and length (burialdate) = 7 and year(burialdatetr) != 0000)
and NOT (burialdate like "Aft %" and length (burialdate) = 7 and year(burialdatetr) != 0000)

and NOT (burialdate like "Abt %" and length (burialdate) = 12 and year(burialdatetr) != 0000 and month(burialdatetr) != 00)
and NOT (burialdate like "Cal %" and length (burialdate) = 12 and year(burialdatetr) != 0000 and month(burialdatetr) != 00)
and NOT (burialdate like "Est %" and length (burialdate) = 12 and year(burialdatetr) != 0000 and month(burialdatetr) != 00)
and NOT (burialdate like "Bef %" and length (burialdate) = 12 and year(burialdatetr) != 0000 and month(burialdatetr) != 00)
and NOT (burialdate like "Aft %" and length (burialdate) = 12 and year(burialdatetr) != 0000 and month(burialdatetr) != 00)

and NOT (burialdate like "Abt %" and length (burialdate) = 15 and year(burialdatetr) != 0000 and month(burialdatetr) != 00 and day(burialdate) != 00)
and NOT (burialdate like "Cal %" and length (burialdate) = 15 and year(burialdatetr) != 0000 and month(burialdatetr) != 00 and day(burialdate) != 00)
and NOT (burialdate like "Est %" and length (burialdate) = 15 and year(burialdatetr) != 0000 and month(burialdatetr) != 00 and day(burialdate) != 00)
and NOT (burialdate like "Bef %" and length (burialdate) = 15 and year(burialdatetr) != 0000 and month(burialdatetr) != 00 and day(burialdate) != 00)
and NOT (burialdate like "Aft %" and length (burialdate) = 15 and year(burialdatetr) != 0000 and month(burialdatetr) != 00 and day(burialdate) != 00)

and NOT (burialdate like "Bet %" and length (burialdate) = 17 and (year(burialdatetr) != 0000 or month(burialdatetr) != 00 or day(burialdatetr) != 00))

and NOT (burialdate like "Bet %" and length (burialdate) = 31 and (year(burialdatetr) != 0000 or month(burialdatetr) != 00 or day(burialdatetr) != 00))


ORDER BY `tng_people`.`burialdate` ASC

/*=======================================*/

/* invalid marriage dates */

SELECT gedcom, familyID, husband, wife, marrdate, marrdatetr, changedby
FROM tng_families
WHERE STR_TO_DATE( marrdate, '%d %b %Y' ) IS NULL
AND STR_TO_DATE( marrdate, '%b %Y' ) IS NULL
AND STR_TO_DATE( marrdate, '%Y' ) IS NULL
AND NOT (marrdate = "y" or marrdate = "n"
) and NOT (marrdate like "Abt %" and length (marrdate) = 8 and year(marrdatetr) != 0000)
and NOT (marrdate like "Cal %" and length (marrdate) = 8 and year(marrdatetr) != 0000)
and NOT (marrdate like "Est %" and length (marrdate) = 8 and year(marrdatetr) != 0000)
and NOT (marrdate like "Bef %" and length (marrdate) = 8 and year(marrdatetr) != 0000)
and NOT (marrdate like "Abt %" and length (marrdate) = 8 and year(marrdatetr) != 0000)
and NOT (marrdate like "Aft %" and length (marrdate) = 8 and year(marrdatetr) != 0000)

and NOT (marrdate like "Abt %" and length (marrdate) = 7 and year(marrdatetr) != 0000)
and NOT (marrdate like "Cal %" and length (marrdate) = 7 and year(marrdatetr) != 0000)
and NOT (marrdate like "Est %" and length (marrdate) = 7 and year(marrdatetr) != 0000)
and NOT (marrdate like "Bef %" and length (marrdate) = 7 and year(marrdatetr) != 0000)
and NOT (marrdate like "Abt %" and length (marrdate) = 7 and year(marrdatetr) != 0000)
and NOT (marrdate like "Aft %" and length (marrdate) = 7 and year(marrdatetr) != 0000)

and NOT (marrdate like "Abt %" and length (marrdate) = 12 and year(marrdatetr) != 0000 and month(marrdatetr) != 00)
and NOT (marrdate like "Cal %" and length (marrdate) = 12 and year(marrdatetr) != 0000 and month(marrdatetr) != 00)
and NOT (marrdate like "Est %" and length (marrdate) = 12 and year(marrdatetr) != 0000 and month(marrdatetr) != 00)
and NOT (marrdate like "Bef %" and length (marrdate) = 12 and year(marrdatetr) != 0000 and month(marrdatetr) != 00)
and NOT (marrdate like "Aft %" and length (marrdate) = 12 and year(marrdatetr) != 0000 and month(marrdatetr) != 00)

and NOT (marrdate like "Abt %" and length (marrdate) = 15 and year(marrdatetr) != 0000 and month(marrdatetr) != 00 and day(marrdate) != 00)
and NOT (marrdate like "Cal %" and length (marrdate) = 15 and year(marrdatetr) != 0000 and month(marrdatetr) != 00 and day(marrdate) != 00)
and NOT (marrdate like "Est %" and length (marrdate) = 15 and year(marrdatetr) != 0000 and month(marrdatetr) != 00 and day(marrdate) != 00)
and NOT (marrdate like "Bef %" and length (marrdate) = 15 and year(marrdatetr) != 0000 and month(marrdatetr) != 00 and day(marrdate) != 00)
and NOT (marrdate like "Aft %" and length (marrdate) = 15 and year(marrdatetr) != 0000 and month(marrdatetr) != 00 and day(marrdate) != 00)

and NOT (marrdate like "Bet %" and length (marrdate) = 17 and (year(marrdatetr) != 0000 or month(marrdatetr) = !00 or day(marrdatetr) != 00))
and NOT (marrdate like "Bet %" and length (marrdate) = 31 and (year(marrdatetr) != 0000 or month(marrdatetr) = !00 or day(marrdatetr) != 00))


ORDER BY `tng_families`.`marrdate` ASC


/*=======================================*/

/* invalid divorce dates */

SELECT gedcom, familyID, husband, wife, divdate, divdatetr, changedby
FROM tng_families
WHERE STR_TO_DATE( divdate, '%d %b %Y' ) IS NULL
AND STR_TO_DATE( divdate, '%b %Y' ) IS NULL
AND STR_TO_DATE( divdate, '%Y' ) IS NULL
AND NOT (divdate = "y" or divdate = "n"
) and NOT (divdate like "Abt %" and length (divdate) = 8 and year(divdatetr) != 0000)
and NOT (divdate like "Cal %" and length (divdate) = 8 and year(divdatetr) != 0000)
and NOT (divdate like "Est %" and length (divdate) = 8 and year(divdatetr) != 0000)
and NOT (divdate like "Bef %" and length (divdate) = 8 and year(divdatetr) != 0000)
and NOT (divdate like "Abt %" and length (divdate) = 8 and year(divdatetr) != 0000)
and NOT (divdate like "Aft %" and length (divdate) = 8 and year(divdatetr) != 0000)

and NOT (divdate like "Abt %" and length (divdate) = 7 and year(divdatetr) != 0000)
and NOT (divdate like "Cal %" and length (divdate) = 7 and year(divdatetr) != 0000)
and NOT (divdate like "Est %" and length (divdate) = 7 and year(divdatetr) != 0000)
and NOT (divdate like "Bef %" and length (divdate) = 7 and year(divdatetr) != 0000)
and NOT (divdate like "Abt %" and length (divdate) = 7 and year(divdatetr) != 0000)
and NOT (divdate like "Aft %" and length (divdate) = 7 and year(divdatetr) != 0000)

and NOT (divdate like "Abt %" and length (divdate) = 12 and year(divdatetr) != 0000 and month(divdatetr) != 00)
and NOT (divdate like "Cal %" and length (divdate) = 12 and year(divdatetr) != 0000 and month(divdatetr) != 00)
and NOT (divdate like "Est %" and length (divdate) = 12 and year(divdatetr) != 0000 and month(divdatetr) != 00)
and NOT (divdate like "Bef %" and length (divdate) = 12 and year(divdatetr) != 0000 and month(divdatetr) != 00)
and NOT (divdate like "Aft %" and length (divdate) = 12 and year(divdatetr) != 0000 and month(divdatetr) != 00)

and NOT (divdate like "Abt %" and length (divdate) = 15 and year(divdatetr) != 0000 and month(divdatetr) != 00 and day(divdate) != 00)
and NOT (divdate like "Cal %" and length (divdate) = 15 and year(divdatetr) != 0000 and month(divdatetr) != 00 and day(divdate) != 00)
and NOT (divdate like "Est %" and length (divdate) = 15 and year(divdatetr) != 0000 and month(divdatetr) != 00 and day(divdate) != 00)
and NOT (divdate like "Bef %" and length (divdate) = 15 and year(divdatetr) != 0000 and month(divdatetr) != 00 and day(divdate) != 00)
and NOT (divdate like "Aft %" and length (divdate) = 15 and year(divdatetr) != 0000 and month(divdatetr) != 00 and day(divdate) != 00)


and NOT (divdate like "Bet %" and length (divdate) = 31 and (year(divdatetr) != 0000 or month(divdatetr) != 00 or day(divdatetr) != 00))
and NOT (divdate like "Bet %" and length (divdate) = 17 and (year(divdatetr) != 0000 or month(divdatetr) != 00 or day(divdatetr) != 00))


ORDER BY `tng_families`.`divdate` ASC
/*=======================================*/

/* invalid event dates */

SELECT gedcom, persfamID, eventtypeID, eventdate, eventdatetr, eventplace, age, agency, cause, addressID, parenttag, info
FROM tng_events
WHERE STR_TO_DATE( eventdate, '%d %b %Y' ) IS NULL
AND STR_TO_DATE( eventdate, '%b %Y' ) IS NULL
AND STR_TO_DATE( eventdate, '%Y' ) IS NULL
AND NOT (eventdate = "y" or eventdate = "n"
) and NOT (eventdate like "Abt %" and length (eventdate) = 8 and year(eventdatetr) != 0000)
and NOT (eventdate like "Cal %" and length (eventdate) = 8 and year(eventdatetr) != 0000)
and NOT (eventdate like "Est %" and length (eventdate) = 8 and year(eventdatetr) != 0000)
and NOT (eventdate like "Bef %" and length (eventdate) = 8 and year(eventdatetr) != 0000)
and NOT (eventdate like "Abt %" and length (eventdate) = 8 and year(eventdatetr) != 0000)
and NOT (eventdate like "Aft %" and length (eventdate) = 8 and year(eventdatetr) != 0000)

and NOT (eventdate like "Abt %" and length (eventdate) = 7 and year(eventdatetr) != 0000)
and NOT (eventdate like "Cal %" and length (eventdate) = 7 and year(eventdatetr) != 0000)
and NOT (eventdate like "Est %" and length (eventdate) = 7 and year(eventdatetr) != 0000)
and NOT (eventdate like "Bef %" and length (eventdate) = 7 and year(eventdatetr) != 0000)
and NOT (eventdate like "Abt %" and length (eventdate) = 7 and year(eventdatetr) != 0000)
and NOT (eventdate like "Aft %" and length (eventdate) = 7 and year(eventdatetr) != 0000)

and NOT (eventdate like "Abt %" and length (eventdate) = 12 and year(eventdatetr) != 0000 and month(eventdatetr) != 00)
and NOT (eventdate like "Cal %" and length (eventdate) = 12 and year(eventdatetr) != 0000 and month(eventdatetr) != 00)
and NOT (eventdate like "Est %" and length (eventdate) = 12 and year(eventdatetr) != 0000 and month(eventdatetr) != 00)
and NOT (eventdate like "Bef %" and length (eventdate) = 12 and year(eventdatetr) != 0000 and month(eventdatetr) != 00)
and NOT (eventdate like "Aft %" and length (eventdate) = 12 and year(eventdatetr) != 0000 and month(eventdatetr) != 00)

and NOT (eventdate like "Abt %" and length (eventdate) = 15 and year(eventdatetr) != 0000 and month(eventdatetr) != 00 and day(eventdate) != 00)
and NOT (eventdate like "Cal %" and length (eventdate) = 15 and year(eventdatetr) != 0000 and month(eventdatetr) != 00 and day(eventdate) != 00)
and NOT (eventdate like "Est %" and length (eventdate) = 15 and year(eventdatetr) != 0000 and month(eventdatetr) != 00 and day(eventdate) != 00)
and NOT (eventdate like "Bef %" and length (eventdate) = 15 and year(eventdatetr) != 0000 and month(eventdatetr) != 00 and day(eventdate) != 00)
and NOT (eventdate like "Aft %" and length (eventdate) = 15 and year(eventdatetr) != 0000 and month(eventdatetr) != 00 and day(eventdate) != 00)

and NOT (eventdate like "Bet %" and length (eventdate) = 17 and (year(eventdatetr) != 0000 OR month(eventdatetr) != 00 OR day(eventdatetr) != 00))
and NOT (eventdate like "Bet %" and length (eventdate) = 31 and (year(eventdatetr) = 0000 OR month(eventdatetr) = 00 OR day(eventdate) = 00))


ORDER BY `tng_events`.`eventdate` ASC