find row that causes cast error

Posted on 2007-07-20
Last Modified: 2010-03-19

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.
Question by:csetzkorn
    LVL 92

    Accepted Solution

    Hello csetzkorn,

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


    LVL 75

    Assisted Solution

    by:Aneesh Retnakaran
    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.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
    When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now