[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

convert string to date

Posted on 2011-10-04
10
Medium Priority
?
618 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:jd11b
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 22

Assisted Solution

by:pivar
pivar earned 400 total points
ID: 36913681
Hi,

Convert to datetime

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

/peter
0
 

Author Comment

by:jd11b
ID: 36914532
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
 
LVL 24

Accepted Solution

by:
johanntagle earned 800 total points
ID: 36914628
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
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
LVL 24

Expert Comment

by:johanntagle
ID: 36914630
0
 
LVL 15

Assisted Solution

by:Insoftservice
Insoftservice earned 800 total points
ID: 36915017
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
 
LVL 24

Expert Comment

by:johanntagle
ID: 36915021
yeah sorry it should be date_format, not format_date.
0
 

Author Closing Comment

by:jd11b
ID: 36916125
Thank you all, awesome works perfectly!!!
0
 

Author Comment

by:jd11b
ID: 36916126
Thank you very much, it's perfect.
0
 
LVL 15

Expert Comment

by:Insoftservice
ID: 36917762
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
 

Author Comment

by:jd11b
ID: 36918094
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

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

590 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