Solved

MySQL update text field to date field

Posted on 2014-01-02
5
577 Views
Last Modified: 2014-01-02
I have a database where the date field is text field and need to fix that.  What is the best way to do that.

If date1 is a text field with 01/01/2014
and date 2 is a date field and needs to display 2014-01-01.

Would you use substr to get the numbers out, something like this conceptually, if so, I have no idea how to put them all together?

update table set date2 = date_format('substr(`date1`, 0, 2)',  '%Y-%m-%d')


Thanks in advance, hope everyone had a great new year!
0
Comment
Question by:ststesting
  • 2
  • 2
5 Comments
 
LVL 2

Author Comment

by:ststesting
ID: 39751630
Just found this.
update table set date2 = STR_TO_DATE(`date1`, '%Y-%m-%d')

and get
Error 1411. Incorrect datetime value 'date1' for function str_to_date
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39751734
Remember the format you enter is one that matches the data in `date`.  Date data type does not store in a specific format; therefore, you are not looking to format it as 2014-01-01, you simply want to convert to a date.
0
 
LVL 28

Accepted Solution

by:
sammySeltzer earned 250 total points
ID: 39751744
can you just try:

STR_TO_DATE(date1, '%Y-/%m-/%d') .

date1 without the quotes?
0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 250 total points
ID: 39751750
I doubt it is the quoted identifiers.  Here is an example of what I mean.
SELECT STR_TO_DATE(date1, '%m/%d/%Y') date2
FROM (
    SELECT CONVERT('01/01/2014', CHAR(10)) date1 
    UNION SELECT '12/30/2013'
) tbl
ORDER BY date2
;

Open in new window

0
 
LVL 2

Author Comment

by:ststesting
ID: 39751901
Thanks guys!

I was using str_to_date like date_format and what putting what I wanted the output to be rather that telling it want it wanted (%Y-/%m-/%d vs %m/%d/%Y).  (dumb moment)

update tbl set date2 = STR_TO_DATE(`date1`, '%m/%d/%Y')

Also this worked.
update tbl set date2 = concat(substr(date1, 7, 4),'-', substr(date1, 4, 2),'-', substr(date1, 4, 2))
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
This video discusses moving either the default database or any database to a new volume.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

760 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

17 Experts available now in Live!

Get 1:1 Help Now