didnt work for us

Hi quedao,

Thank you for reporting this error. I have not been able to reproduce but have released a potential fix today. This error could be related to strict settings or the version of MySQL installed on your site.

Please let me know if version 2.1.14 resolves this error.

Thank you.

Hi Rhett,

I’m having the same problem:

[Incorrect DATETIME value: '']
SELECT * -- ,(SELECT GROUP_CONCAT(DISTINCT post_title) FROM wp_posts WHERE FIND_IN_SET(ID,product_ids)>0)product FROM ( SELECT p.ID post_id,post_title coupon_code ,(SELECT meta_value FROM wp_postmeta WHERE post_id=p.ID AND meta_key='product_ids' LIMIT 1)product_ids ,(SELECT meta_value FROM wp_postmeta WHERE post_id=p.ID AND meta_key='exclude_product_ids')exc_prds ,(SELECT meta_value FROM wp_postmeta WHERE post_id=p.ID AND meta_key='product_categories')cats ,(SELECT meta_value FROM wp_postmeta WHERE post_id=p.ID AND meta_key='exclude_product_categories')exc_cats ,(SELECT meta_value FROM wp_postmeta WHERE post_id=p.ID AND meta_key='individual_use' LIMIT 1)individual ,(SELECT meta_value FROM wp_postmeta WHERE post_id=p.ID AND meta_key='coupon_amount' LIMIT 1)coupon_amount ,(SELECT meta_value FROM wp_postmeta WHERE post_id=p.ID AND meta_key='_wc_min_qty' LIMIT 1)min_qty ,(SELECT meta_value FROM wp_postmeta WHERE post_id=p.ID AND meta_key='_wc_max_qty' LIMIT 1)max_qty ,(SELECT meta_value FROM wp_postmeta WHERE post_id=p.ID AND meta_key='_wc_qty_ntf' LIMIT 1)qty_ntf ,(SELECT meta_key FROM wp_postmeta WHERE post_id=p.ID AND meta_key LIKE '_wc_%_apply' AND meta_value='yes' LIMIT 1)apply ,DATE_FORMAT(FROM_UNIXTIME(x.meta_value),'%m-%d-%Y')exp_date ,CASE WHEN FROM_UNIXTIME(x.meta_value)<NOW() THEN 1 ELSE 0 END exp FROM wp_posts p LEFT JOIN wp_postmeta x ON x.post_id=p.ID AND x.meta_key='date_expires' AND LENGTH(x.meta_value)=10 AND x.meta_value REGEXP '[0-9]' WHERE post_type='shop_coupon' AND post_status='publish' AND IFNULL(NULLIF(FROM_UNIXTIME(x.meta_value),''),NOW())>=NOW() )a WHERE(individual='yes' OR apply IS NOT NULL) ORDER BY exp,individual DESC,CAST(coupon_amount AS SIGNED) DESC;

Currently on MySQL 8.0.27

Just as test I’ve created another DB using MySQL 5.7.23 and imported the same DB. This works fine, the coupon are added correctly and the “[Incorrect DATETIME value: ”]” error is gone.

I’ve created and used this patch on my project.

diff --git a/wac_functions.php b/wac_functions.php
index 6e26b11..1e120e5 100644
--- a/wac_functions.php
+++ b/wac_functions.php
@@ -139,7 +139,7 @@ function wac_apply_coupons() {
   }
   
   if($trb<1) {
-    $req.=" AND IFNULL(NULLIF(FROM_UNIXTIME(x.meta_value),''),NOW())>=NOW()";
+    $req.=" AND IFNULL(FROM_UNIXTIME(x.meta_value),NOW())>=NOW()";
     $req2.=" WHERE(individual='yes' OR apply IS NOT NULL)";
   }

Hi Matt,

Thank you for this information. I’ll try to include this update as soon as possible.
What was the MySQL version you were using when you originally received this error?

Hey Rhett,

I encountered the issue using MySQL 8.0.27
It works fine with MySQL 5.7.23

Thanks for this awesome plugin!

Mattia

Hi Matt,
I really appreciate you finding this bug and associating it with a version of MySQL. I’ve identified the issue and will be releasing it with a slight revision from yours. Can you confirm this will still work?

- $req.=" AND IFNULL(NULLIF(FROM_UNIXTIME(x.meta_value),''),NOW())>=NOW()";
+ $req.=" AND IFNULL(FROM_UNIXTIME(NULLIF(x.meta_value,'')),NOW())>=NOW()";

This issue has been addressed in version 2.1.18. Thank you @mattiamaragno!

Hey Rhett,

Yep, that seems to work for me on both versions MySQL 8.0.27 and Mysql 5.7.23.

Thanks!