• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 599
  • Last Modified:

MySQL update text field to date field

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
ststesting
Asked:
ststesting
  • 2
  • 2
2 Solutions
 
ststestingAuthor Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
sammySeltzerCommented:
can you just try:

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

date1 without the quotes?
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
ststestingAuthor Commented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now