Convert date in varchar field

I moved data from one database to another, one of the fields that was moved is a date and put into a varchar field. I won't get into details but it has to be like that.

Right now it displays the date as:  Jan  1 2014 12:00AM

I need to update the whole column for all records so that it reads as:  mm/dd/yyyy  (01/01/2014)

SQL Something like:

Update users
set DOB = NEW value where DOB = 'current value'

Help is appreciated
LVL 1
AleksAsked:
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.

David KrollCommented:
First try this:

select dob, convert(varchar, dob, 101) as newdob
from users

if that gives the desired result, then:

update users
set dob = convert(varchar, dob, 101)
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
UPDATE users
set DOB = format(convert(datetime, 'Feb 3 2014 1:00PM'),'MM/dd/yyyy')
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
I'm assuming that, since your destination field is DOB, and your target field is DOB, then you are converting a varchar to a varchar. If not, use David's solution.
0
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

AleksAuthor Commented:
I am using MS SQL 2008, tried the above got this error:

Msg 195, Level 15, State 10, Line 2
'format' is not a recognized built-in function name.

and what is that date in the query ?   I don't know what the date is, it changes on every row and I need to convert the date in all rows.
0
AleksAuthor Commented:
I tried david's query and it display the date the exact same as before.

Jan  1 2014 12:00AM
0
David KrollCommented:
select convert(varchar, convert(datetime, DOB), 101)
0
AleksAuthor Commented:
There is something wrong in the statement

Msg 207, Level 16, State 1, Line 15
Invalid column name 'DOB'.
0
David KrollCommented:
DOB will be the name of the column you want to convert

select convert(varchar, convert(datetime, fieldnametoconvert), 101)
from table
0
AleksAuthor Commented:
I tried that and got the error above.
0
AleksAuthor Commented:
The query you entered above doesn't specify the table, I think that might be necessary.
0
David KrollCommented:
can you post the exact statement you're trying to run?
0
David KrollCommented:
Yes, of course it's necessary :)
0
AleksAuthor Commented:
This actually shows the date in the correct format, so .. how do we update the column to have that value ?

select convert(varchar, convert(datetime, Dob), 101)
 from BDotSiskindSusser.dbo.Users
 WHERE DOB IS NOT null

the above only selects, we need to UPDATE  :)
0
AleksAuthor Commented:
Any help on how to do the update statement ?  the above select statement looks good, now I need to update the value to the one displayed by the above select  :)
0
David KrollCommented:
update BDotSiskindSusser.dbo.Users
set Dob =  convert(varchar, convert(datetime, Dob), 101)
 from BDotSiskindSusser.dbo.Users
 WHERE DOB IS NOT null
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
AleksAuthor Commented:
Thank you  !
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.