Blocking '0000-00-00' from MySQL Date Fields

mysql datetime date data-cleaning
David picture David · Oct 8, 2010 · Viewed 11.7k times · Source

I have a database where old code likes to insert '0000-00-00' in Date and DateTime columns instead of a real date. So I have the following two questions:

  1. Is there anything that I could do on the db level to block this? I know that I can set a column to be not-null, but that does not seem to be blocking these zero values.
  2. What is the best way to detect the existing zero values in date fields? I have about a hundred tables with 2-3 date columns each and I don't want to query them individually.

Followup:

The default is already set to null. A long time ago, the default was '0000-00-00'. Some code still explicitly places '0000-00-00'. I would prefer to force that code to throw an error so I could isolate and remove it.

Answer

OMG Ponies picture OMG Ponies · Oct 8, 2010

Is there anything that I could do on the db level to block this?

Yes, enable the NO_ZERO_DATE mode:

SET sql_mode = 'NO_ZERO_DATE';

The behaviour is documented. Additionally, you might want to also set the mode to include NO_ZERO_IN_DATE...

Also make sure the sql_mode includes either STRICT_ALL_TABLES or STRICT_TRANS_TABLES; without these NO_ZERO_IN_DATE only give a warning, but insert still succeeds.

What is the best way to detect the existing zero values in date fields? I have about a hundred tables with 2-3 date columns each and I don't want to query them individually.

Separate columns means they have to be checked individually--nothing you can do about that.

Related questions

MySQL Query GROUP BY day / month / year

Is it possible to make a simple query to count how many records I have in a determined period of time like a year, month, or day, having a TIMESTAMP field, like: SELECT COUNT(id) FROM stats WHERE record_date.…

Read More
How to select date from datetime column?

I have a column of type "datetime" with values like 2009-10-20 10:00:00 I would like to extract date from datetime and write a query like: SELECT * FROM data WHERE datetime = '2009-10-20' ORDER BY datetime DESC Is the …

Read More
Difference between two dates in MySQL

How to calculate the difference between two dates, in the format YYYY-MM-DD hh: mm: ss and to get the result in seconds or milliseconds?

Read More