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

Question

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 1

If it doesn't matter what date goes in there (ie as long as it's non-zero) you can change the column definition to use NOW() as the default. Probably not an ideal solution, but it does satisfy the criteria : 1) Not-null 2) Non-zero I'm actually really not proud of that suggestion

Answer 2

You could make the columns nullable and have NULL as the default value, but it sounds like you already have that and it's not working. ALTHOUGH... it could be the tool you're using to display the data doesn't like displaying NULL dates... what tool are you using? Or is the '0000-00-00' showing up in data retreived by code?

You could set a default value that is non-null and also easily recognizable as a default such as 1900-01-01 (assuming you don't normally deal with dates that are close to this date).

Answer 3

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...

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.

Answer 4

Assuming you can't easily fix the data and "SET sql_mode = 'NO_ZERO_DATE';", you could create a view on the table...

CREATE VIEW filter AS
SELECT other_column, 
CASE 
  WHEN realtable.dodgy_date = 0 THEN NULL 
  ELSE realtable.dodgy_date
END AS dodgy_date
FROM realtable;

Answer 5

A trigger can be used to enforce values for columns.