convert varchar to datetime

I have a hard time converting a varchar field with a string like "20051123" to a datetime value into a new field.

Test SQL:

stringdate is a varchar field with a string like "20051123"nin table tableval:

select CONVERT(datetime, stringdate, 103) as newdate from tableval

select CAST( stringdate as datetime ) as newdate from tableval

got error message:The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Please help.

Thanks

amy

That just means that you have some data that are not valid SQL Server dates. Try this query to find the bad data:

SELECT stringdate FROM tableval
WHERE ISDATE(stringdate) = 0

Thank you nosepicker. I will try it.

amy