find row that causes cast error

Posted on 2007-07-20
Medium Priority
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 93

Accepted Solution

Patrick Matthews earned 260 total points
ID: 19530541
Hello csetzkorn,

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


LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 240 total points
ID: 19530550
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

840 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