CODEWITHSUNDEEP
mysqlmariadb
kittygirl
kittygirl

Reputation: 2443

how to find 00 in date row and replace it in mysql database?

Because I set NO_ZERO_IN_DATE in sql_mode,I want to use 

SELECT *
  FROM tbl_name 
 WHERE date_column regexp '[0-9]\d{4}-00-00' 
 order 
    by id

to find the 00-** or **-00 or 00-00 date column and replace the 00 to 01.

But my script doesn't work.

Thanks in advance for any solution.

Upvotes: 0

Views: 42

Answers (1)

Rup
Rup

Reputation: 34418

You can use the date and time functions e.g. DAYOFMONTH to extract the components of the date so that you can test them:

SELECT * FROM tbl_name
 WHERE DAYOFMONTH(date_column) = 0 OR MONTH(date_column) = 0 
ORDER BY ID

You would need to convert the date to a string before you can match it against a regexp, and it's simpler to just test the date components directly.

Upvotes: 1

Related Questions