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
Solved

Convert date in varchar field

Posted on 2014-12-05
16
149 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
  • 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

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 …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

856 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