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

Question

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?

Answer 1

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}$')

Answer 2

I just thought.. maybe it would work if I write :

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

(I am running the command atm, the table is very big, but it should normally stop immediately if it wouldn't work)

Answer 3

First SELECT records which have incorrect date format and then UPDATE those:

UPDATE `Table1` t
JOIN (
  SELECT * FROM `Table1`
  WHERE str_to_date( `date`, '%d-%M-%Y' ) IS NOT NULL
) t2
ON t.id = t2.id
SET t.`date` = str_to_date( t.`date`, '%d-%M-%Y' )

Working Demo: http://sqlfiddle.com/#!2/f01565/1

Using str_to_date in UPDATE statement gives error, following will not work:

UPDATE `Table1` 
SET `date` = 
  CASE WHEN str_to_date( `date`, '%d-%M-%Y' ) IS NOT NULL
       THEN str_to_date( `date`, '%d-%M-%Y' )
       ELSE `date`
  END

Answer 4

Another way you can perform this task is using COALESCE function. which gives you first not null value from the provided values. you can create your query as following.

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

This will first try to convert date to given format. if the string is not in the %d-%M-%Y format it will produce null so it will automatically chose date as a value. if it is in the format it will take str_to_date( date, '%d-%M-%Y') as a value since it is first value and not null.