0

My website is running on Fortrabbit with the following settings: PHP version 7.4.15 OS version Linux 4.15.0-50-generic Database driver & version MySQL 8.0.23 Image driver & version Imagick 3.4.4 (ImageMagick 7.0.10-62) Craft edition & version Craft Pro 3.7.13 Yii version 2.0.43 Twig version v2.14.7 Guzzle version 7.3.0

I seem to get this mysql datetime value error whenever I have this code run: {% for order in craft.orders.isCompleted().isPaid().dateOrdered(">= #{product.productCountSalesFrom}").hasPurchasables(product.tickets).limit(9999).withLineItems().all() %}, which i suspect might have to do with .dateOrdered(">= #{product.productCountSalesFrom}") this part particularly, where productCountSalesFrom is a date time field in Craft CMS.

The error I get is as follows: PDOException: SQLSTATE[HY000]: General error: 1525 Incorrect DATETIME value: '' in /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/db/Command.php:1302 Stack trace: #0 /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/db/Command.php(1302): PDOStatement->execute() #1 /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/db/Command.php(1168): yii\db\Command->internalExecute('SELECT `element...') #2 /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/db/Command.php(410): yii\db\Command->queryInternal('fetchAll', NULL) #3 /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/db/Query.php(249): yii\db\Command->queryAll() #4 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/db/Query.php(152): yii\db\Query->all(NULL) #5 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/elements/db/ElementQuery.php(1593): craft\db\Query->all(NULL) #6 /srv/app/testingamss2/htdocs/vendor/twig/twig/src/Extension/CoreExtension.php(1511): craft\elements\db\ElementQuery->all() #7 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/helpers/Template.php(106): twig_get_attribute(Object(craft\web\twig\Environment), Object(Twig\Source), Object(craft\commerce\elements\db\OrderQuery), 'all', Array, 'method', false, false) #8 /srv/app/testingamss2/htdocs/storage/runtime/compiled_templates/a3/a3e2c9b6d960ab31b4c5bf12daf4fa2b1ef942569d0745ace091d41396f04418.php(60): craft\helpers\Template::attribute(Object(craft\web\twig\Environment), Object(Twig\Source), Object(craft\commerce\elements\db\OrderQuery), 'all', Array, 'method') #9 /srv/app/testingamss2/htdocs/vendor/twig/twig/src/Template.php(405): __TwigTemplate_d24e2c5e2158ee994ad3eda22d2f0682868b9f509316210fce079587488718b4->doDisplay(Array, Array) #10 /srv/app/testingamss2/htdocs/vendor/twig/twig/src/Template.php(378): Twig\Template->displayWithErrorHandling(Array, Array) #11 /srv/app/testingamss2/htdocs/vendor/twig/twig/src/Template.php(390): Twig\Template->display(Array) #12 /srv/app/testingamss2/htdocs/vendor/twig/twig/src/TemplateWrapper.php(45): Twig\Template->render(Array, Array) #13 /srv/app/testingamss2/htdocs/vendor/twig/twig/src/Environment.php(318): Twig\TemplateWrapper->render(Array) #14 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/web/View.php(408): Twig\Environment->render('events/generalE...', Array) #15 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/web/View.php(461): craft\web\View->renderTemplate('events/generalE...', Array) #16 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/web/Controller.php(257): craft\web\View->renderPageTemplate('events/generalE...', Array, 'site') #17 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/controllers/TemplatesController.php(100): craft\web\Controller->renderTemplate('events/generalE...', Array) #18 [internal function]: craft\controllers\TemplatesController->actionRender('events/generalE...', Array) #19 /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array) #20 /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/base/Controller.php(181): yii\base\InlineAction->runWithParams(Array) #21 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/web/Controller.php(190): yii\base\Controller->runAction('render', Array) #22 /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/base/Module.php(534): craft\web\Controller->runAction('render', Array) #23 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/web/Application.php(277): yii\base\Module->runAction('templates/rende...', Array) #24 /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/web/Application.php(104): craft\web\Application->runAction('templates/rende...', Array) #25 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/web/Application.php(262): yii\web\Application->handleRequest(Object(craft\web\Request)) #26 /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/base/Application.php(392): craft\web\Application->handleRequest(Object(craft\web\Request)) #27 /srv/app/testingamss2/htdocs/web/index.php(26): yii\base\Application->run() #28 {main}

Next yii\db\Exception: SQLSTATE[HY000]: General error: 1525 Incorrect DATETIME value: '' The SQL being executed was: SELECT elements.id, elements.fieldLayoutId, elements.uid, elements.enabled, elements.archived, elements.dateCreated, elements.dateUpdated, elements_sites.id AS siteSettingsId, elements_sites.slug, elements_sites.siteId, elements_sites.uri, elements_sites.enabled AS enabledForSite, elements.canonicalId, elements.dateLastMerged, commerce_orders.id, commerce_orders.number, commerce_orders.reference, commerce_orders.couponCode, commerce_orders.orderStatusId, commerce_orders.dateOrdered, commerce_orders.email, commerce_orders.isCompleted, commerce_orders.datePaid, commerce_orders.currency, commerce_orders.paymentCurrency, commerce_orders.lastIp, commerce_orders.orderLanguage, commerce_orders.message, commerce_orders.returnUrl, commerce_orders.cancelUrl, commerce_orders.billingAddressId, commerce_orders.shippingAddressId, commerce_orders.estimatedBillingAddressId, commerce_orders.estimatedShippingAddressId, commerce_orders.shippingMethodHandle, commerce_orders.gatewayId, commerce_orders.paymentSourceId, commerce_orders.customerId, commerce_orders.dateUpdated, commerce_orders.registerUserOnOrderComplete, commerce_orders.recalculationMode, commerce_orders.origin, commerce_orders.dateAuthorized, commerce_orders.totalPrice AS storedTotalPrice, commerce_orders.totalPaid AS storedTotalPaid, commerce_orders.itemTotal AS storedItemTotal, commerce_orders.totalDiscount AS storedTotalDiscount, commerce_orders.totalShippingCost AS storedTotalShippingCost, commerce_orders.totalTax AS storedTotalTax, commerce_orders.totalTaxIncluded AS storedTotalTaxIncluded, commerce_orders.shippingMethodName, commerce_orders.itemSubtotal AS storedItemSubtotal, commerce_orders.orderSiteId, content.id AS contentId, content.field_newsAuthor, content.field_productAvailableFrom, content.field_productAvailableUntil, content.field_benefit, content.field_benefitInstructions, content.field_benefitLocation, content.field_calendarUrl_kctenffu, content.field_colour, content.field_productCountSalesFrom, content.field_userDob, content.field_linkDescription, content.field_hideFromNavigation, content.field_userHonours, content.field_sponsorMajorPartner_msnpclqg, content.field_maximumQuantity_novxyhdv, content.field_membershipRequired_akszozdk, content.field_productMembershipRequired, content.field_productOnePerMember, content.field_userPersonalEmail, content.field_userPhoneNumber, content.field_userRegisterAmasa, content.field_userRegisterAmsf, content.field_sponsorUrl_nmcpasgw, content.field_ticketName_rbqbnltw, content.field_userYearLevel FROM (SELECT elements.id AS elementsId, elements_sites.id AS elementsSitesId, content.id AS contentId FROM craft_elements elements INNER JOIN craft_commerce_orders commerce_orders ON commerce_orders.id = elements.id LEFT JOIN craft_commerce_addresses billing_address ON billing_address.id = commerce_orders.billingAddressId LEFT JOIN craft_commerce_addresses shipping_address ON shipping_address.id = commerce_orders.shippingAddressId INNER JOIN craft_elements_sites elements_sites ON elements_sites.elementId = elements.id INNER JOIN craft_content content ON content.elementId = elements.id WHERE (commerce_orders.isCompleted=TRUE) AND (commerce_orders.dateOrdered >= '') AND (commerce_orders.totalPaid >= commerce_orders.totalPrice) AND (EXISTS (SELECT * FROM craft_commerce_lineitems lineitems WHERE (lineitems.orderId = elements.id) AND (lineitems.purchasableId IN (11141, 16448)))) AND (elements.type='craft\commerce\elements\Order') AND (elements.archived=FALSE) AND (elements.dateDeleted IS NULL) AND (elements.draftId IS NULL) AND (elements.revisionId IS NULL) ORDER BY commerce_orders.id LIMIT 9999) subquery INNER JOIN craft_commerce_orders commerce_orders ON commerce_orders.id = subquery.elementsId LEFT JOIN craft_commerce_addresses billing_address ON billing_address.id = commerce_orders.billingAddressId LEFT JOIN craft_commerce_addresses shipping_address ON shipping_address.id = commerce_orders.shippingAddressId INNER JOIN craft_elements elements ON elements.id = subquery.elementsId INNER JOIN craft_elements_sites elements_sites ON elements_sites.id = subquery.elementsSitesId INNER JOIN craft_content content ON content.id = subquery.contentId ORDER BY commerce_orders.id in /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/db/Schema.php:678 Stack trace: #0 /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/db/Command.php(1307): yii\db\Schema->convertException(Object(PDOException), 'SELECT element...') #1 /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/db/Command.php(1168): yii\db\Command->internalExecute('SELECT element...') #2 /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/db/Command.php(410): yii\db\Command->queryInternal('fetchAll', NULL) #3 /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/db/Query.php(249): yii\db\Command->queryAll() #4 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/db/Query.php(152): yii\db\Query->all(NULL) #5 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/elements/db/ElementQuery.php(1593): craft\db\Query->all(NULL) #6 /srv/app/testingamss2/htdocs/vendor/twig/twig/src/Extension/CoreExtension.php(1511): craft\elements\db\ElementQuery->all() #7 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/helpers/Template.php(106): twig_get_attribute(Object(craft\web\twig\Environment), Object(Twig\Source), Object(craft\commerce\elements\db\OrderQuery), 'all', Array, 'method', false, false) #8 /srv/app/testingamss2/htdocs/storage/runtime/compiled_templates/a3/a3e2c9b6d960ab31b4c5bf12daf4fa2b1ef942569d0745ace091d41396f04418.php(60): craft\helpers\Template::attribute(Object(craft\web\twig\Environment), Object(Twig\Source), Object(craft\commerce\elements\db\OrderQuery), 'all', Array, 'method') #9 /srv/app/testingamss2/htdocs/vendor/twig/twig/src/Template.php(405): __TwigTemplate_d24e2c5e2158ee994ad3eda22d2f0682868b9f509316210fce079587488718b4->doDisplay(Array, Array) #10 /srv/app/testingamss2/htdocs/vendor/twig/twig/src/Template.php(378): Twig\Template->displayWithErrorHandling(Array, Array) #11 /srv/app/testingamss2/htdocs/vendor/twig/twig/src/Template.php(390): Twig\Template->display(Array) #12 /srv/app/testingamss2/htdocs/vendor/twig/twig/src/TemplateWrapper.php(45): Twig\Template->render(Array, Array) #13 /srv/app/testingamss2/htdocs/vendor/twig/twig/src/Environment.php(318): Twig\TemplateWrapper->render(Array) #14 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/web/View.php(408): Twig\Environment->render('events/generalE...', Array) #15 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/web/View.php(461): craft\web\View->renderTemplate('events/generalE...', Array) #16 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/web/Controller.php(257): craft\web\View->renderPageTemplate('events/generalE...', Array, 'site') #17 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/controllers/TemplatesController.php(100): craft\web\Controller->renderTemplate('events/generalE...', Array) #18 [internal function]: craft\controllers\TemplatesController->actionRender('events/generalE...', Array) #19 /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array) #20 /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/base/Controller.php(181): yii\base\InlineAction->runWithParams(Array) #21 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/web/Controller.php(190): yii\base\Controller->runAction('render', Array) #22 /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/base/Module.php(534): craft\web\Controller->runAction('render', Array) #23 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/web/Application.php(277): yii\base\Module->runAction('templates/rende...', Array) #24 /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/web/Application.php(104): craft\web\Application->runAction('templates/rende...', Array) #25 /srv/app/testingamss2/htdocs/vendor/craftcms/cms/src/web/Application.php(262): yii\web\Application->handleRequest(Object(craft\web\Request)) #26 /srv/app/testingamss2/htdocs/vendor/yiisoft/yii2/base/Application.php(392): craft\web\Application->handleRequest(Object(craft\web\Request)) #27 /srv/app/testingamss2/htdocs/web/index.php(26): yii\base\Application->run() #28 {main} Additional Information: Array ( [0] => HY000 [1] => 1525 [2] => Incorrect DATETIME value: '' )

I do not get this error on my local server, however, mysql on my local sever is on 5.5.5, and I suspect it may be because of this difference in mysql version.

Any help on how to resolve this would be much appreciated! Thankyou in advance

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.