I have a table which contains a column that is of datatype VARCHAR(256). In this table I have string values and valid datetime strings in the format 'YYYY-MM-DD HH:MM:SS.mmm'. I wish to query for date after having eliminated all those records where the column is not a date. I used ISDATE() to remove the non-date values and then I try to SELECT those records that are older than today.
When I have a subquery, my expectation is that it gets evaluated in the order that it was specified. I have no problem with the Query Optimizer clubs all my WHERE clauses willy nilly, but if I have a view or a subquery, don't you think you should maintain the order?
Everything below this line refers to the implementation code given in "Steps to Reproduce"
Attempts 1 2 and 3 were lame. I fully understand why these 3 didn't work.
But Attempt 4 explicitly calls out that I want to apply the ISDATE() function first and then and only then apply the < GETDATE() predicate.
Same thing with Attempt 5. You would think that when a user has gone to the level of creating a view, that the data returned from the view will only contain dates. How else is one to partition tables horizontally if you are going to apply the WHERE clause to within the view?
Attempt 6 uses subqueries for IN. According to BOL, this should work, but doesn't.
Attempt 7 is one way of getting this accomplished, but I hate using temp tables - especially since the end users usually don't have rights to create temp tables.
Attempt 8 is Microsoft's official "best" response. This was given to me by the Tech who worked on this issue with me. Case #: REG:109101469019749.
As a side note, I fully understand the fact that a column should not contain dates mixed in with other values. This is an audit table and is auditing column value changes for before and after. Sometimes the column that changed happens to be a date column in which case, the column will contain a valid date format.
Thanks in advance,