Solved

query to format dates on MySQL

Posted on 2014-10-07
12
411 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
  • 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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 500 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 54

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Color can increase conversions, create feelings of warmth or even incite people to get behind a cause. If you want your website to really impact site visitors, then it is vital to consider the impact color has on them.
This tutorial demonstrates how to identify and create boundary or building outlines in Google Maps. In this example, I outline the boundaries of an enclosed skatepark within a community park.  Login to your Google Account, then  Google for "Google M…
The viewer will learn how to count occurrences of each item in an array.

803 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