Solved

Convert date in varchar field

Posted on 2014-12-05
16
151 Views
Last Modified: 2014-12-05
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
0
Comment
Question by:amucinobluedot
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 2
16 Comments
 
LVL 11

Expert Comment

by:David Kroll
ID: 40482877
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40482881
UPDATE users
set DOB = format(convert(datetime, 'Feb 3 2014 1:00PM'),'MM/dd/yyyy')
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40482885
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
Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

 

Author Comment

by:amucinobluedot
ID: 40482908
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
 

Author Comment

by:amucinobluedot
ID: 40482918
I tried david's query and it display the date the exact same as before.

Jan  1 2014 12:00AM
0
 
LVL 11

Expert Comment

by:David Kroll
ID: 40482937
select convert(varchar, convert(datetime, DOB), 101)
0
 

Author Comment

by:amucinobluedot
ID: 40482943
There is something wrong in the statement

Msg 207, Level 16, State 1, Line 15
Invalid column name 'DOB'.
0
 
LVL 11

Expert Comment

by:David Kroll
ID: 40482991
DOB will be the name of the column you want to convert

select convert(varchar, convert(datetime, fieldnametoconvert), 101)
from table
0
 

Author Comment

by:amucinobluedot
ID: 40483086
I tried that and got the error above.
0
 

Author Comment

by:amucinobluedot
ID: 40483088
The query you entered above doesn't specify the table, I think that might be necessary.
0
 
LVL 11

Expert Comment

by:David Kroll
ID: 40483090
can you post the exact statement you're trying to run?
0
 
LVL 11

Expert Comment

by:David Kroll
ID: 40483094
Yes, of course it's necessary :)
0
 

Author Comment

by:amucinobluedot
ID: 40483140
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
 

Author Comment

by:amucinobluedot
ID: 40483234
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
 
LVL 11

Accepted Solution

by:
David Kroll earned 500 total points
ID: 40483560
update BDotSiskindSusser.dbo.Users
set Dob =  convert(varchar, convert(datetime, Dob), 101)
 from BDotSiskindSusser.dbo.Users
 WHERE DOB IS NOT null
0
 

Author Closing Comment

by:amucinobluedot
ID: 40483940
Thank you  !
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question