CODEWITHSUNDEEP
mysqldatestr-to-date
adrCoder
adrCoder

Reputation: 3285

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?

Upvotes: 2

Views: 1399

Answers (4)

Vikum Dheemantha
Vikum Dheemantha

Reputation: 843

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.

Upvotes: 0

Aziz Shaikh
Aziz Shaikh

Reputation: 16544

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

Upvotes: 4

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44874

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

Upvotes: 1

adrCoder
adrCoder

Reputation: 3285

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)

Upvotes: 0

Related Questions