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

Handling custom date fields in your database

Zen Cart uses a specific string (rather than NULL) for date fields to indicate that the date 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 convert these older style dates (0000-00-00) to a newer value (0001-01-01), which is acceptable to mySQL.

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.

An example of a plugin which adds a date field is Order Delivery Date. It adds a field called order_delivery_date to the orders table.

To get the custom 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';

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

Still have questions? No problem! Just 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 August 31, 2020 by Scott C Wilson (4ce199e).