Help with out-of-range value

Question

I have this query that is giving me an error

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Query:

select 
    COUNT(*) 
from 
    dbo.patient
INNER JOIN 
    dbo.study on dbo.patient.pk = dbo.study.patient_fk
              and dbo.study.study_datetime IS NOT NULL
              and dbo.patient.pat_birthdate IS NOT NULL
              and dbo.study.study_datetime <= DATEADD(D, -2192, GETDATE())
              and dbo.patient.pat_birthdate <= DATEADD(D, -7670, GETDATE());

I read where this might help to be converted to 'DATETIME2', but I can't quite figure out how to correctly convert the column 's.study_datetime' as this column more than likely has some bad values as entries.

Aaron Bertrand · Accepted Answer · 2020-05-07T18:40:20.383

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:

Help with out-of-range value

1 Answers1

Linked