Continue running SQL even after an error in where clause

Question

All,

The last statement in this code block gives me the DATE conversion error. My requirement is to run the statement even after the last statement fails. How can I achieve this?

SELECT USERID FROM IMSIDENTITYPRE   
WHERE IDENTITYSTATUS IN ('ACTIVATED','INITIATED','SUSPENDED')      
AND AUTHORITATIVESOURCE IN('WD', 'SP' )          
AND USERID NOT IN (SELECT IDENTITYUSERID FROM WDIdentityPRE)      
AND USERID NOT IN (SELECT GDD FROM ADIdentityPRE)  
AND CAST(AssignmentStartDate AS DATE)<=GETDATE()  

Answer 1

Adding to @Viorel 's answer, TRY_CAST (or TRY_CONVERT) will return NULL when the proported date column value cannot be converted to date. The predicate TRY_CAST(AssignmentStartDate AS DATE)<=GETDATE() will not be satisfied for invalid dates because the result is UNKNOWN instead of true of false.

SELECT USERID FROM IMSIDENTITYPRE   
WHERE  
    IDENTITYSTATUS IN ('ACTIVATED','INITIATED','SUSPENDED')      
    AND AUTHORITATIVESOURCE IN('WD', 'SP' )          
    AND USERID NOT IN (SELECT IDENTITYUSERID FROM WDIdentityPRE)      
    AND USERID NOT IN (SELECT GDD FROM ADIdentityPRE)  
    AND TRY_CAST(AssignmentStartDate AS DATE)<=GETDATE();  

Consider changing the AssignmentStartDate column to a date data type to ensure invalid dates cannot be stored. This will provide data integrity, improve performance, and reduce storage needs.

Answer 2

Use TRY_CAST instead of CAST. The bad rows will be excluded.