Finding row causing error in type conversion in SQL Server

date-conversion, sql, sql-server

Solution

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

Problem

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.

Original source