Error: Incorrect DATETIME value …. SELECT SQL_CALC_FOUND_ROWS

This issue has come up before in the forums but there is no clear solution about it yet.

The full error is:

WordPress database error Incorrect DATETIME value: '' for query SELECT SQL_CALC_FOUND_ROWS  (wp_tec_occurrences.occurrence_id + 10000000) as occurrence_id
FROM wp_posts LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = '_EventHideFromUpcoming' )
JOIN wp_tec_occurrences ON wp_posts.ID = wp_tec_occurrences.post_id
WHERE 1=1  AND (
wp_tec_occurrences.start_date > ''
AND
wp_postmeta.post_id IS NULL
) AND wp_posts.post_type = 'tribe_events' AND ((wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')) AND (wp_posts.ID NOT IN (22653))
GROUP BY wp_tec_occurrences.occurrence_id
ORDER BY wp_tec_occurrences.start_date ASC, wp_posts.post_date ASC
LIMIT 0, 1
made by require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('/plugins/the-events-calendar/src/views/v2/default-template.php'), Tribe\Events\Views\V2\Template_Bootstrap->get_view_html, Tribe\Events\Views\V2\Template_Bootstrap->get_v1_single_event_html, tribe_get_view, include('/themes/child-theme/tribe-events/single-event.php'), tribe_the_next_event_link, tribe_get_next_event_link, Tribe__Events__Adjacent_Events->get_next_event_link, Tribe__Events__Adjacent_Events->get_event_link, Tribe__Events__Adjacent_Events->get_closest_event, WP_Query->get_posts, apply_filters_ref_array('posts_pre_query'), WP_Hook->apply_filters, TEC\Events\Custom_Tables\V1\WP_Query\Modifiers\Events_Only_Modifier->filter_posts_pre_query, TEC\Events\Custom_Tables\V1\WP_Query\Custom_Tables_Query->get_posts, WP_Query->get_posts, QM_DB->query

This error always happens twice in a row.

It’s already been discovered that it is related to functions for get_next_event_link and get_prev_event_link.

Here is the new information I’ve discovered about this from my side. This error ONLY happens when a link is opened with a certain format for a venue.

Normally a venue URL would look something like this: /venue/venue-name. This does not cause the error to happen. But sometimes a link is used that looks like this:
/?eventDisplay=venue&tribe_venue=12345&post_type=trive_venue

Here is the interesting thing, both of those URLs load the same template file plugins/the-events-calendar/src/views/v2/default-template.php. However, within the page itself, the normal link is treated like a venue but the longer link is treated as if it is showing an event, not a venue. In fact, at the top of the page I can see the output “This event has passed” which should not be showing on a venue page, only an event page. I think this is why the error is triggered, because it’s loading all the page elements for an event when it’s supposed to be a venue.

Here is what I found about how that longer link is output.

1. When I go to a vendor page, it shows the current events, and a previous and next button.
2. If the Next button is disabled, just click the Previous button first, then on the page that opens, Next should be enabled.
3. The link of the Next button will be the longer “bad” link style.
   • If I simply click Next at this point (the bad link), the URL will actually rewrite itself to the standard permalink style and the page opens perfectly normally.
   • If I right-click the Next button and click to open in a new tab, or if I copy the link and paste it in a new tab, it does NOT rewrite, and it opens the bad page template and triggers the error. The page thinks I’m loading an event instead of a vendor.

I have no idea why opening the link directly by copy/paste or new tab does NOT rewrite it, and simply clicking the link does rewrite it, but this is the issue!

I don’t know if the bug is in how the default template parses the page. Or if it is something broken in not rewritting the URL when used directly. I don’t know if that longer link style shows up in other contexts as well.

I hope this is enough info to be able to duplicate the issue and find a solution. I have TEC 6.13.2.1 and TEC Pro 7.6.0.2. It is licensed but I’m just a dev helping them out so I can’t do a normal support ticket or log in to their account. Hopefully this message is enough cause I’m not going to report this all over again in another system.

Thanks!