October 8th, 2013, 05:39 AM
Help with a quick table query
I have 'inherited' a project where the database / dates are a bit screwed up.
I have been handed a simple table with the column titles...
MySqlDate (which i added)
The id is the id, the InputDate unfortunately is a tinytext where the input format is for example...
I have added the MySqlDate as a date column to make everything easier for new entries / queries and all is good.
Now I need to convert / update the existing data from the InputDate column to the respective MySqlDate column.
i.e. from tinytext to date - 0000-00-00
Got me stumped.
Any tips appreciated.
October 8th, 2013, 12:45 PM
the problem you're going to face is that if you can have both 08/20 and 20/08 in the text column, then how the heck are you going to decide whether 04/03 is april 3rd or march 4th?
once you can answer that, the conversion update should be easy
October 14th, 2013, 10:34 AM
the dates in the example are not like that. they are both in European format with the date first and the month second.
you should be able to update the MySqlDate column using the input date, just parse it by the '/'
something like :
SUBSTRING_INDEX(inputdate,'/',1) for the Date
you may have to do some casting to get it to be a real number
October 14th, 2013, 03:56 PM
oh, that's right, i just noticed
Originally Posted by bobert123
okay, STR_TO_DATE will do the job nicely