Your varchar columns have bad data in them, and you can't really control when SQL Server will try to evaluate them (before or after any filtering occurs). So you can add where clauses that prevent them from being included (by checking for only rows where the value is a date), but there is still no guarantee of the order in which the where clauses will be evaluated (meaning you may still get an error). So in addition to those filters (which can logically replace your NOT NULL filters), you can also ensure that the predicates are only evaluated when they are, in fact, valid dates.
SELECT COUNT(*)
FROM dbo.patient AS p
INNER JOIN dbo.study AS s
ON s.patient_fk = p.pk
WHERE ISDATE(p.pat_birthdate) = 1
AND ISDATE(s.study_datetime) = 1
AND CASE WHEN ISDATE(s.study_datetime) = 1 THEN s.study_datetime END
<= DATEADD(DAY, -2192, GETDATE())
AND CASE WHEN ISDATE(p.pat_birthdate) = 1 THEN p.pat_birthdate END
<= DATEADD(DAY, -7670, GETDATE());
Next, please fix the table so that random junk can no longer find its way into these columns. Identify the bad data first using:
SELECT * FROM dbo.study
WHERE ISDATE(study_datetime) = 0;
SELECT * FROM dbo.patient
WHERE ISDATE(pat_birthdate) = 0;
Then correct accordingly (they may all be "dates" in the same pattern, or it really may be a bunch of random junk that you just need to set to NULL). Once there are no bad values in there, you can change the data type to DATE or DATETIME, as it should have been from the start.
Please see the following posts:
