Conditional str_to_date() if format is of one type, else do nothing

I have a table with dates, some of which have this format 31-DEC-2010 while others have this format 2011-01-13.

I am trying to get all them having the date format, using the str_to_date() function, but it fails since it can not convert 2011-01-13 (some of the dates are already in the correct format because I ran this command previously but then I added more data)

UPDATE `Table1` SET `date` = str_to_date( `date`, '%d-%M-%Y' );

Is there some way to run this command only on the rows that have this format?

Solution

You should think of changing the data type to date and store dates in mysql fomat that will make life simple.

Now if you do a str_to_date() with a date format and the input is not in the format then it will return null.

mysql> select str_to_date( '2011-01-13', '%d-%M-%Y' ) as date;
+------+
| date |
+------+
| NULL |
+------+

So you can do the trick as

update 
`Table1` 
SET `date` = case when str_to_date( `date`, '%d-%M-%Y' ) is null then date 
else str_to_date( `date`, '%d-%M-%Y' ) end

UPDATE

This might fall into a warning as

mysql> select str_to_date( '2011-01-13', '%d-%M-%Y' );
+-----------------------------------------+
| str_to_date( '2011-01-13', '%d-%M-%Y' ) |
+-----------------------------------------+
| NULL                                    |
+-----------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings ;
+---------+------+-----------------------------------------------------------------+
| Level   | Code | Message                                                         |
+---------+------+-----------------------------------------------------------------+
| Warning | 1411 | Incorrect datetime value: '2011-01-13' for function str_to_date |
+---------+------+-----------------------------------------------------------------+
1 row in set (0.01 sec)

So the other approach is to use regex for the update

update 
`Table1` 
SET `date` = str_to_date( `date`, '%d-%M-%Y' )
where `date` not REGEXP('^[0-9]{4}-[0-9]{1,2}-[0-9]{1,2}$')