convert to a valid datetime

have a field that has date value, but the datatype is nvarchar(50)The value is 08/04/2006 23:58:51. When I use the convert to datetime statement, it errors our with the foll error:

How do I convert the datatype to a valid datetime?

select convert(datetime ,08/04/2006 23:58:51)- this errors out with the foll err:

Arithmetic overflow error converting expression to data type datetime.

Both of these work:

DECLARE @.MyDate nvarchar(50)

SET @.MyDate = '08/04/2006 23:58:51'

SELECT CONVERT(datetime ,@.MyDate)

SELECT CONVERT(datetime ,'08/04/2006 23:58:51')

|||

i already use the second and when come to record 500000 this error apper

i check the row nothing change

|||Does that record only contain this exact value '08/04/2006 23:58:51'?...no special characters?|||Use this:

UPDATE

SET DateField = CASE WHEN ISDATE(CharField) THEN CONVERT(datetime ,CharField) ELSE NULL END

Then:

SELECT * FROM TABLE WHERE DateField IS NULL AND CharField IS NOT NULL

And see what is not working and handle them by hand.

|||Thanks Tom it works