How to identify which row is giving me an out of range error

[bmacbmac]

Hi. I have about 3,000,000 rows of data. I have a date column with the data in format MMDDYYYY. I am trying to convert to datetime format MM-DD-YYYY 00:00:00.000.

I am getting the error: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

Is there any way I can find out which row is giving me this error without looking through all the rows. I'm thinking there must be some search or query I can run to identify.

This is SQL 2000.

Thanks!

Brian

 

[drlex]

Sounds like a job for ISDATE().
A query like

SELECT * from TABLE_NAME WHERE LEN(DATE_FIELD) <> 8 OR 
(LEN(DATE_FIELD) = 8 AND ISDATE(LEFT(DATE_FIELD,2)+ '-' + SUBSTRING(DATE_FIELD,3,2) + '-' + RIGHT(DATE_FIELD,4)) = 0)

should produce your 31st of September or entries like 112011 for 1st Jan 2011.


soi là, soi carré