Solved

query to format dates on MySQL

Posted on 2014-10-07
12
417 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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 57

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

When crafting your “Why Us” page, there are a plethora of pitfalls to avoid. Follow these five tips, and you’ll be well on your way to creating an effective page.
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
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:
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

687 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