?
Solved

query to format dates on MySQL

Posted on 2014-10-07
12
Medium Priority
?
421 Views
Last Modified: 2014-10-07
I am familiar with MS SQL but not sure about MySQL.

I have a field which is currently a varchar255 and holds dates in the following format:

Mon - Jun 28, 02:51 pm

I tried to just change the field type to 'datetime' but I get an error and it won't let me.

How can I format the field so that it is in a format that allows me to change the field to "datetime"

the table name is:  'emails'
the field name is: Created Date'
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
  • 6
  • 5
12 Comments
 
LVL 58

Expert Comment

by:Gary
ID: 40366227
This would be easier in PHP (and what about the year?)
0
 

Author Comment

by:amucinobluedot
ID: 40366235
I am not following. I am talking about MySQL database. I need to change the data type in the database so I can run queries in the database later. if its a varchar I can't compare dates.
0
 

Author Comment

by:amucinobluedot
ID: 40366248
About the year, well ... apparently is missing, we can just set those all up for 2014
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 58

Expert Comment

by:Gary
ID: 40366274
When selecting the date you will have to prepend it with 2014 and replace AM and PM with :00 AM / :00 PM respectively
SELECT STR_TO_DATE('2014 Mon - Jun 28, 02:51:00 PM','%Y %a - %b %d, %r');

Open in new window

0
 

Author Comment

by:amucinobluedot
ID: 40366286
I REALLY need to convert the data in the database, not in the PHP file.  Any ideas ?
0
 
LVL 58

Expert Comment

by:Gary
ID: 40366289
That is in mysql
0
 

Author Comment

by:amucinobluedot
ID: 40366295
Umm .. if I run that query it will only affect one entry right ?
there are hundreds of entries with different dates

Will that affect all records?
0
 
LVL 58

Expert Comment

by:Gary
ID: 40366310
Make a backup of the table and run these queries, I'm assuming the field is Created_Date and not Created Date (a space)

UPDATE emails set Created_Date = REPLACE(Created_Date,' am',':00 AM');
UPDATE emails set Created_Date = REPLACE(Created_Date,' pm',':00 PM');
UPDATE emails set Created_Date = CONCAT('2014 ',Created_Date);

UPDATE emails set Created_Date = (SELECT STR_TO_DATE(Created_Date,'%Y %a - %b %d, %r'));

Open in new window

0
 

Author Comment

by:amucinobluedot
ID: 40366312
I was able to update all dates and now I have this:

2013,Fri - Sep 26, 01:35 am

I still cannot select 'datetime' and save the table. How can I reformat all those dates in the above format to a correct format ?
0
 
LVL 58

Accepted Solution

by:
Gary earned 2000 total points
ID: 40366320
Make a backup of the table and run this

UPDATE emails set Created_Date = REPLACE(Created_Date,' am',':00 AM');
UPDATE emails set Created_Date = REPLACE(Created_Date,' pm',':00 PM');

UPDATE emails set Created_Date = (SELECT STR_TO_DATE(Created_Date,'%Y,%a - %b %d, %r'));

Open in new window

0
 
LVL 58

Expert Comment

by:Julian Hansen
ID: 40366328
How about this
SELECT STR_TO_DATE(CONCAT('2014 ', MID(`date`, 1, LENGTH(`date`)-6),':00', RIGHT(`date`,3)), '%Y %W - %M %d, %r') FROM table;

Open in new window

Assumes that `date` is the field in your table
0
 

Author Comment

by:amucinobluedot
ID: 40366333
Perfect ! thank you !
0

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Viewers will get an overview of the benefits and risks of using Bitcoin to accept payments. What Bitcoin is: Legality: Risks: Benefits: Which businesses are best suited?: Other things you should know: How to get started:
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

801 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