Solved

Convert date in varchar field

Posted on 2014-12-05
16
152 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

688 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