How can I find out which data won't cast?

Question

I have a SQL table with a date field defined as char(8), or 20090609, and a time field defined as char(4), or 1230. I am moving this data into another table and I want to combine the two fields and put them in a smalldatetime field in the new table. My query is like this:

INSERT NewTable(eventdate) 
SELECT
  CAST((datecol + ' ' + substring(timecol, 1, 2) + ':' + substring(timecol, 3, 2)) as smalldatetime)
FROM OldTable

When I run this, I get an error:

The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

I've tried checking len(datecol) and len(timecol) to make sure that they are at least the correct number of characters. I have no idea how I can find the offending data, any suggestions? The database is SQL2000 and I'm using SMO 2008.

Answer 1

If you run the query in query analyzer it should tell you what row the error occured!

Answer 2

It is probably out of the range of acceptable smalldatetime values January 1, 1900, through June 6, 2079

EDIT On closer inspection I think the substring parameters for the second portion of the time may be incorrect (which may be the whole problem) updated below to reflect substring(timecol, 3, 2)

New Approach this sql does assume that all dates are 8 characters in length, and all times are 4.

Select SubString(DateCol, 1, 4) as tehYear, 
Substring(DateCol, 5,2) as tehMonth, 
SubString(DateCol, 7,2) as tehDay,
SubString(TimeCol, 1,2) as tehHour,
Substring(TimeCOl, 3,4) as tehMinute,
*
from OldTable
where
(SubString(DateCol, 1,4) > 9999 or SubString(DateCol, 1,4) < 1753)
OR (Substring(DateCol, 5,2) > 12 or Substring(DateCol, 5,2) < 1)
OR (SubString(DateCol, 7,2) > 31 or SubString(DateCol, 7,2) < 1)
OR (SubString(TimeCol, 1,2) > 23 or(SubString(TimeCol, 1,2) < 0)
OR (Substring(TimeCOl, 3,4) > 59 or Substring(TimeCOl, 3,4) <0)

Try casting to datetime and seeing if there are any dates that fall outside of that range to identify your problem data.

    SELECT
    CAST((datecol + ' ' + substring(timecol, 1, 2) + ':' + substring(timecol, 3, 2))
    as datetime)
    FROM OldTable 
    Where CAST((datecol + ' ' + substring(timecol, 1, 2) 
    + ':' + substring(timecol, 3, 2)) as datetime) 
    > Cast('06/06/2079' as datetime) or CAST((datecol + ' ' 
    + substring(timecol, 1, 2) + ':' + substring(timecol, 3, 2)) as datetime)
    < Cast('01/01/1900' as datetime)

Answer 3

Try this:

SELECT datecol, timecol
FROM OldTable
WHERE ISDATE(datecol + ' ' + substring(timecol, 1, 2) + ':' + substring(timecol, 2, 2)) = 0

That will show you which rows cannot be converted successfully.