find row that causes cast error


I have a huge table (all fields are varchar(max), which I want to insert into a smaller table (all fields are smalldatetime).

I use this:

cast( dbo.transformDate( DATE ) as smalldatetime )

to cast the original varchar(max) fields. This works fine for the majority of fields. Unfortunately, one or several rows contain 'rubbish'. I am just wondering what the best strategy is to find the culprit row(s) given my very large table.

Many thanks.


PS: The UDF transformDate works fine.
Who is Participating?
Patrick MatthewsCommented:
Hello csetzkorn,

FROM SomeTable
WHERE ISDATE(DateColumn) = 0   --1=is convertible to a date, 0=not


Aneesh RetnakaranDatabase AdministratorCommented:
u can make use of the ISDATE() function

ISDATE('urString') will return 0 if that string is not a valid date , otherwise it will retun 1.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.