[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1104
  • Last Modified:

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!
0
robthomas09
Asked:
robthomas09
  • 3
  • 3
  • 2
2 Solutions
 
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
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now