Date standardization

Getting custom date fields set correctly

Note: This guidance is only required if you have modified the structure of your database to add custom datetime or date fields.

Handling custom datetime/date fields in your database

Zen Cart uses a specific string (rather than NULL) for datetime and date fields to indicate that a value has not been set.

In older versions of Zen Cart, the string was 0000-00-00. However, since then, MySQL has updated their definition of allowable date formats to exclude this string.

Since Zen Cart 1.5.6, the upgrade process has included steps necessary to fix these older style dates as follows

  • update all rows in the table to change any date columns with the value 0000-00-00 to 0001-01-01, and set the field’s default value to 0001-01-01.
  • update all rows in the table to change any datetime columns with the value 0000-00-00 00:00:00 to 0001-01-01 00:00:00, and set the field’s default value to 0001-01-01 00:00:00.

(In the case of datetime values, the appended value for time remains 00:00:00; it has not changed.)

However, the upgrade process can only do this for built-in fields. If you have customized your database to include additional fields (perhaps via a plugin, or perhaps with your own custom code), you will need to make the same changes to your own database.

Examples of plugins which add a datetime or date field are:

  • Order Delivery Date. It adds a datetime field called order_delivery_date to the orders table. (Note: Some earlier versions of the plugin created this field as a date.)

  • Ceon Back In Stock Notifications. It creates a datetime field called date_subscribed in a new table called back_in_stock_notification_subscriptions.

To get the custom datetime field order_delivery_date into the new format, use following command in phpMyAdmin or in Install SQL Patches:

UPDATE orders SET order_delivery_date = '0001-01-01 00:00:00' WHERE order_delivery_date < '0001-01-01' and order_delivery_date is not null;

Then update the default value using

ALTER TABLE orders MODIFY COLUMN order_delivery_date datetime NOT NULL default '0001-01-01 00:00:00';

To fix a custom field which is a date (instead of a datetime), the process is similar:

UPDATE thetablename SET thefieldname = '0001-01-01' WHERE thefieldname < '0001-01-01'; 

Then update the default value of the field:

ALTER TABLE thetablename MODIFY COLUMN thefieldname date NOT NULL default '0001-01-01';

Note: If you run that command via phpMyAdmin and your site uses a DB_PREFIX (e.g. zen_), you will need to add that prefix to any database table name. For the example above, you’ll replace orders with zen_orders.

For reference, the script that does the date updating is stored in zc_install/sql/install/zero_dates_cleanup.sql.

Still have questions? Use the Search box in the upper right, or try the full list of FAQs. If you can't find it there, head over to the Zen Cart support forum and ask there in the appropriate subforum. In your post, please include your Zen Cart and PHP versions, and a link to your site.

Is there an error or omission on this page? Please post to General Questions on the support forum. Or, if you'd like to open a pull request, just review the guidelines and get started. You can even PR right here.
Last modified December 1, 2021 by Scott C Wilson (019b5615).