Menu
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:
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.
767
The obvious way to fix the error is to change the formatting of your value into the format that MySQL can accept. But rather than editing the value manually, you can use the STR_TO_DATE() function to help you convert the string value into date value.
If you need to select rows from a MySQL database' table in a date range, you need to use a command like this: SELECT * FROM table WHERE date_column >= '2014-01-01' AND date_column <= '2015-01-01';
The following is the output. The following is the query to format the date to YYYY-MM-DD. mysql> select str_to_date(LoginDate,'%d. %m.
MySQL retrieves and displays DATE values in ' YYYY-MM-DD ' format. The supported range is '1000-01-01' to '9999-12-31' .
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.
96
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
