Found an oddity that I’m hoping @r937 can explain to me but if anyone else has insight, I’d appreciate understanding why this occurred.
I have a legacy system that stores date values in strings instead of date fields(much to my chagrin).
I have a field where the value is ALWAYS a valid date, and the query I ran into this oddity looks for dates between 60 and 180 days old. For some reason, this month, the query got an error.
I was able to work around it because we have that particular date stored on another table as an actual, you know, date. I know, I know…two tables, same data…why? Simple answer is this product is normalization hell
But what really threw me is if I move that DATEDIFF statement into the select statement and remove it from the where clause, the select statement returns data for each and every record.
I resolved it by using the date field we had in another table (see horrendous normalization comment above) since it removed the whole conversion portion and just needed the DATEDIFF.
This thread is more to eliminate a point of confusion for me as to why the convert would fail in the WHERE clause but not when used in the select list.
Well I was hoping that TRY_CONVERT would point to a row where the conversion failed, which might shed some light on what had happened. But its good that it got resolved.
Nope, as I fixed it by taking the convert out and using the date field instead. It was just an oddity I thought you might have an explanation for…curious minds and all that.