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