MySQL - Change date string to date type in place?

Question

(Hi all. I'm new-ish to SQL and MySQL in particular. I know some PHP.)

I have a table which contains a "date_string" column. For legacy reasons, this is a text field, containing the date in d/m/YY format. e.g. "22/11/09" for 22nd November 2009.

Question: how could I convert all fields in this column to a standard MySQL date format (YYYY-mm-dd), in-place?

Or, failing being able to change it in place, given that the column type is text, how could I take each existing text-based date, and create a standard date in another column (of type 'date') within the table?

Thanks for any help.

Answer 1

You probably want to use the STR_TO_DATE() function.

SELECT STR_TO_DATE(textdate, '%d/%m/%y') FROM MyTable...

Or create another column with DATE data type and copy the values:

ALTER TABLE MyTable ADD COLUMN realdate DATE;
UPDATE MyTable SET realdate = STR_TO_DATE(textdate, '%d/%m/%y');

Answer 2

    UPDATE your_table
    SET your_col = '20'
              + substring(your_col, 
                          LOCATE('/', your_col, LOCATE('/', your_col) + 1) + 1
                         )
              + '-'
              + substring(your_col, 
                          LOCATE('/', your_col) + 1, 
                          LOCATE('/', your_col, LOCATE('/', your_col) + 1)
                         )
              + '-'
              + substring(your_col, 1, LOCATE('/', your_col) - 1)


/*
    LOCATE('/', your_col) -> search for 1st occurence of '/' in your_col
    LOCATE('/', your_col, LOCATE('/', your_col) + 1) -> search for 2nd occurence of '/' in your_col
    LOCATE('/', your_col, LOCATE('/', your_col) + 1) + 1 -> from where to retriev the last part
*/

(I suppose year >= 2000)

Answer 3

if you have trouble doing this from within MySQL, you could try with php by converting the dates using strtotime()