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!
robthomas09Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Om PrakashCommented:
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
0
robthomas09Author Commented:
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.

0
Om PrakashCommented:
It might be possible that the field contains invalid date formats. Try the following:
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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

SharathData EngineerCommented:

Is the date_of_birth column defined as datetime or not?
I assume some non-date values are stored in the table. Check for that data.
select date_of_birth
  from Person
 where isdate(date_of_birth) = 0
0
robthomas09Author Commented:
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
0
SharathData EngineerCommented:
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

0
robthomas09Author Commented:
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.
0
Om PrakashCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.