Solved

query to format dates on MySQL

Posted on 2014-10-07
12
408 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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 51

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 to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now