SQL Server DateDiff oddity

[DaveMaxwell]

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.

So my question comes down to why the DATEDIFF fails in the where clause but works in the select?

[Thallius]

Maybe the converted date is not valid? Have you checked all dates?

[DaveMaxwell]

I did.

• All of the records returned a 1 for the ISDATE() function.
• All records seemingly reported a valid date out of the CONVERT function
• All records had a valid value for the DATEDIFF

Unless the SELECT is dropping any invalid rows, I’m confused. But I expected to get an error in the SELECT as well, but I don’t.

[m_hutley]

What happens if you use TRY_CONVERT instead?

[DaveMaxwell]

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.

[m_hutley]

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.

[DaveMaxwell]

It’s a good thought, and for edification, I tried it, and it returns 0 records that fail the TRY_CONVERT.

Putting it in the TRY_CONVERT into the where clause makes the where clause work, even without the NULL check, so

[r937]

@DaveMaxwell

Found an oddity that I’m hoping @r937 can explain to me

nope, i can’t see why you got the error, sorry

is this problem important enough to create a fiddle?

[DaveMaxwell]

@r937

is this problem important enough to create a fiddle?

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.

Thanks anyway, all!