convert string to date

I need to create a mysql query that will convert a field from string to date.  The string originates from an email which takes the value and inserts into the table.  The data is always in this format;
Tue Oct 04 19:01:43 2011
How can I convert this to a date that would match mm/dd/yy in my query?
Thanks in advance.
John
jd11bAsked:
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.

pivarCommented:
Hi,

Convert to datetime

STR_TO_DATE('Tue Oct 04 19:01:43 2011', '%a %b %d %T %Y')

/peter
0
jd11bAuthor Commented:
Thank you, I have to figure out how to display the date as mm/dd/yy by simply running a select query
This format returns '2011-10-04 19:01:43' and I don't know how to get it to return 10/04/11. I appreciate your answer and I should have been more clear in my question as I do not want to update the table.
John
0
johanntagleCommented:
Okay so it is stored as a string?  Quick solution would be:

FORMAT_DATE(STR_TO_DATE('Tue Oct 04 19:01:43 2011', '%a %b %d %T %Y'),'%m/%d/%y')


Suggest though that you convert the column to date or datetime, then use str_to_date when you save data, so you only have to call format_date when you need to read it.
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

johanntagleCommented:
0
InsoftserviceCommented:
hi,

try out this one

SELECT DATE_FORMAT(STR_TO_DATE('Tue Oct 04 19:01:43 2011', '%a %b %d %T %Y'),'%m/%d/%y') AS formatdate
0
johanntagleCommented:
yeah sorry it should be date_format, not format_date.
0
jd11bAuthor Commented:
Thank you all, awesome works perfectly!!!
0
jd11bAuthor Commented:
Thank you very much, it's perfect.
0
InsoftserviceCommented:
hi,

may i know the reason.
how come it came to be assisted when on other statement it has been mention that it was written from date_format to format_date. whereas my code was upto the mark if i am not wrong
0
jd11bAuthor Commented:
Certainly and I hope I did not offend.  It was a group effort and I appreciated everyones assistance.  Seemed like everyone helped throughout and I tried to weigth the points heavier for both you and johantaggle as you both provided a more workable solution.  I really couldn't fault the first person as I could have defined the question better.  Thank you again, I really do appreciate the help. : )
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
MySQL Server

From novice to tech pro — start learning today.