'MySQL - Change date string to date type in place?

(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.

sqlmysqldate


Solution 1:[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');

Solution 2:[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)

Solution 3:[3]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2
Solution 3

Related Questions