varchar convert to datetime

[EBee]

update voterreg
set dob =
case [date of birth]
when isdate([date of birth]) then convert (datetime,[date of birth])
else null
end

I am getting error with this update
Date of Birth : 07/26/1978 --- varchar(15)

i need to convert 15 million records to datetime, I created another field in the same table called dob and would like to update it using the script. it seems to be missing more controls. . . need help

thank you

 

[smin]

Should'nt the case statement be:

case isdate([date of birth])
when 1 then convert (datetime,[date of birth])
else null
end

or

case when isdate([date of birth]) = 1
then convert(datetime,[date of birth])
else null
end

The date '07/26/1978' should not give you any error. There must be something else that causes the error.

What was the error message?

 

[SQLBill]

What is the error you are getting?

You might want to start your script with:

SET DATEFORMAT mdy

That tells SQL Server how to interpret the date information you are providing.

-SQLBill