Solved

query to format dates on MySQL

Posted on 2014-10-07
12
414 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 56

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
Although a lot of people devote their energy toward marketing for specific industries, there are some basic principles that can be applied to any sector imaginable. We’ll look at four steps to take and examine how those steps were put into action fo…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.

730 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