Solved

Convert date in varchar field

Posted on 2014-12-05
16
147 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
 

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…

914 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now