mysqldatestr-to-date

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