Finding row causing error in type conversion in SQL Server

Question

I am trying to cast a column as smalldatetime from varchar. There are some rows containing errors, how can I find them?

SELECT 
    PA.EAR_TAG 
    ,ISNULL(B.DISPOSAL_DATE, H.DISPOSAL_DATE) as HB_Date
    ,Y.[DATE OF MOVEMENT] as Y_Date
    ,DATEDIFF(DAY, ISNULL(B.DISPOSAL_DATE, H.DISPOSAL_DATE), cast(Y.[DATE OF MOVEMENT] as smalldatetime))
FROM
    DairyTelomere.dbo.PROJECT_ANIMALS AS PA
LEFT JOIN 
    Langhill.dbo.YOUNG_STOCK_BULL AS B ON Pa.EAR_TAG = B.EAR_TAG
LEFT JOIN      
    Langhill.dbo.YOUNG_STOCK_HEIFER AS H ON PA.EAR_TAG = H.EAR_TAG
LEFT JOIN      
    DairyTelomere.dbo.Young_Stock_culls AS Y ON PA.EAR_TAG = Y.Ear_Tag

The error I get is:

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value.

I know that if the column was in a date format I could check it by using ISDATE() but unfortunately I can't change the column type (don't have permissions).

Any ideas will be greatly appreciated.

Answer 1

you can use the isdate to get a list of all the ones that are not converting for you. You dont need to change the column type to use this so i am confused by your statement

if the column was in a date format I could check it by using ISDATE() but unfortunately I can't change the column type (don't have permissions)

Will help more if you can clarify but this query should get you a list of rows that have bad date values.

select table.date_as_varchar
from table
where isdate(table.date_as_varchar) = 0