asked on

SQL Syntax - convert varchar m/d/yyyy to date time

Hello experts,

I have a desire to convert the string date of birth fields in SQL to datetime. There are a hefty amount of null values along with a m/d/yyyy current format:

Table: Person
person_id date_of_birth
00001 2/15/1982
00002 02/15/1982
00003 2/5/1981
00004
00005 2/15/1982
00006 1/12/1933
00007 6/6/1917

I am trying to run:
update Person
set date_of_birth =
case when date_of_birth is not null
then ISNULL(convert(char(8),convert(datetime,date_of_birth,101),112),'')
else '' end

but I get the error:
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.

Thoughts?

Thanks!

Om Prakash

Assuming you want to update column date_of_birth1 which is datetime,
update Person
set date_of_birth1 = convert(datetime,date_of_birth,101)
where date_of_birth is not null

robthomas09

ASKER

Thanks for the quick response!

I ran it, but still receive:
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting datetime from character string.

ASKER CERTIFIED SOLUTION

Om Prakash

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

robthomas09

ASKER

I did have some padded blank spaces in there and removed them with:
UPDATE Person SET date_of_birth = REPLACE(date_of_birth,' ','');

Still getting the error - the column is nvarchar. Thanks

Sharath S

did you get any records with my query?

SELECT date_of_birth 
FROM   person 
WHERE  Isdate(Ltrim(Rtrim(date_of_birth))) = 0

Open in new window

robthomas09

ASKER

yes - plenty of blank spaces - I did run om_prakash_p query just now and had some luck. Thanks to both of you for helping.

Om Prakash

This should work

update Person
set date_of_birth1 = convert(datetime,date_of_birth,101)
where date_of_birth is not null and isdate(date_of_birth) = 1