SQL Comparing 2 date values (one of which is stored in a varchar field and might not contain Date data)

Question

I need to compare a date value within a where clause i.e. where a certain date is later than another. The problem arising is that the date which I am comparing against is stored in a varchar field. To doubly complicate matters a simple CONVERT will not work as the data stored in this varchar field is not always a date value and can be any old gubbins (this is an existing DB design and cannot be modified unfortunately. Ahh the joys of legacy design/code.)

Here's a rough example of what I currently have:

SELECT A.Value, B.Value, B.Value2
FROM Table A
JOIN Table B ON B.Id = A.Id
WHERE A.Value3 = 'Some String'
    AND ISDATE(B.GubbinsField) = 1 AND CONVERT(DATETIME, B.GubbinsField, 120) >= A.DateField

Does anyone have a potential solution to this problem of how I can successfully check these values?

Answer 1

Well you are on the right path all you need to do is to get the column data type same on both sides of the Comparison Operator.

When you do CONVERT(DATETIME, B.GubbinsField, 120) >= A.DateField

basically you are comparing a string value with a datetime value. Sql server will try to convert the String to Datetime value because of its higher presidence.

You need to write your query something like....

;WITH CTE 
 AS (
    SELECT A.Value ValueA
         , B.Value ValueB
         , B.Value2
         , A.DateField
         ,B.GubbinsField
    FROM Table A
    JOIN Table B ON B.Id = A.Id
    WHERE A.Value3 = 'Some String'
      AND ISDATE(B.GubbinsField) = 1 
    )
SELECT ValueA, ValueB, Value2
FROM CTE
WHERE CAST(GubbinsField AS DATETIME) >= DateField