My date columns are different because of time included in one column but not the other. How do I fix-CodePudding

I've tried to use

TO_CHAR(ACTIVE_DT, 'YYYY-MM-DD'),
TO_CHAR(CONTRACT_DATE, 'YYYY-MM-DD')

And then a CASE statement to find out which dates do not match.

However when I do this it's telling me a lot of them don't match when they should because the contract date field has time in it and the active date does not. But I have used TO_CHAR to try and fix it. I'm not sure what else to try.

CASE
    WHEN CONTRACT_DATE = ACTIVE_DT 
        THEN 'Correct' 
    WHEN CONTRACT_DATE <> ACTIVE_DT 
        THEN 'Error' 
    ELSE ' ' END 
AS "QC"

CodePudding user response：

If one column has a non-midnight time component and the other column always has a midnight time component then you can match on a 24-hour range (starting from the column at midnight until a day later):

CASE
WHEN CONTRACT_DATE IS NULL
OR   ACTIVE_DATE IS NULL
THEN ' '
WHEN CONTRACT_DATE >= ACTIVE_DT
AND  CONTRACT_DATE <  ACTIVE_DT   INTERVAL '1' DAY
THEN 'Correct'
ELSE 'Error'
END AS "QC"

Alternatively, you can use TRUNC (but that may not use the indexes on the columns):

CASE
WHEN CONTRACT_DATE IS NULL
OR   ACTIVE_DATE IS NULL
THEN ' '
WHEN TRUNC(CONTRACT_DATE) = TRUNC(ACTIVE_DT)
THEN 'Correct'
ELSE 'Error'
END AS "QC"

CodePudding user response：

In Oracle, you have to use TRUNC with the appropriate format to truncate your date value, something like:

    TRUNC(ACTIVE_DT, 'DD')